I've got an SQL database (SQLAzure V12 version running on Azure) that looks like the following:
DECLARE @VehicleData TABLE ( id int PRIMARY KEY, Registration nvarchar(50) NOT NULL, [DateTime] DateTime NOT NULL, [Event] nvarchar(20) NOT NULL, [Data] nvarchar(200) NULL, INDEX IX1 CLUSTERED(Registration, Datetime), INDEX IX2 NONCLUSTERED(Registration, Event, DateTime) )
An auxiliary table looks like this:
DECLARE @EventList TABLE ( id int PRIMARY KEY, [EventName] nvarchar(20) NOT NULL )
The table is filled with location data taken from tracking units on multiple vehicles. Each row might have a particular [Event] type,
e.g. Update, HarshBraking, Delivery etc.
I have a query that needs to get the latest date for each event for a particular vehicle BEFORE A SPECIFIED DATE.
I'm using the following query to do that:
@Registration nvarchar(20), @DateTime datetime SELECT V.Registration, V.DateTime, V.Event, V.Data FROM VehicleData V INNER JOIN ( SELECT Registration, MAX(DateTime) LatestDate, Event FROM VehicleData WHERE Registration = @Registration AND DateTime < @DateTime AND Event in (SELECT Name FROM EventList) GROUP BY Registration, Event ) AS L ON V.Registration = L.Registration AND V.Event = L.Event AND V.DateTime = L.LatestDate
Note the line
AND Event in (SELECT Name FROM EventList) was added later as it forced the query to use the IX2 index, which seemed to improve the query speed.
The query takes some time to run (around 10 seconds or so) and I would ideally like to see if I can optimise it further to take the speed down to less than a second (as it is tied to a GUI action).
How can I optimise this query further?
Edit You can download the execution plan from http://pastebin.com/9nVZzYPL. Just paste the contents into a .sqlplan file and then load it into SSMS to view the plan. Query 1 is the only thing of interest here. The other queries are "post processing" operations that don't contribute much to the overall query time.