1

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.

1

How about using windowing functions?

WITH Numbered AS
(
SELECT *, ROW_NUMBER() OVER (PARTITON BY Event ORDER BY DateTime DESC) AS rownum
FROM VehicleData
WHERE Registration = @Registration 
AND DateTime < @DateTime
)
SELECT V.Registration, V.DateTime, V.Event, V.Data
FROM Numbered V
WHERE rownum = 1;

Make sure you have an index on: (Registration, Event, DateTime) INCLUDE (Data)

You can use your extra predicate on EventList too if you like, of course.

0

The approach I usually use for this type of "get the last record per group" query looks something like the following.

WITH V AS
(
    SELECT VD.Registration, VD.DateTime, VD.Event, VD.Data
    FROM VehicleData VD
    WHERE VD.Registration = @Registration
        AND VD.DateTime < @DateTime
        AND VD.Event in (SELECT Name FROM EventList) 
)
SELECT V.Registration, V.DateTime, V.Event, V.Data
FROM V
    LEFT OUTER JOIN V AS V2
        ON V.Event = V2.Event
        AND V.DateTime < V2.DateTime
WHERE V2.Registration IS NULL

The CTE filters the results to just those for the vehicle you're interested in that occurred before the specified date. The self outer join then pairs each row with all rows of the same event type that occurred after itself. So the only row for a given event that finds no match in V2 is the last one.

This approach avoids any grouping, which may provide a significant speedup. It's difficult to say for sure, especially without an execution plan, but I've always been satisfied with the performance of this type of approach.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.