Efficient limit result set in SQL window function

Question

My question would be better served as a comment on Limit result set in sql window function , but I don't have the necessary reputation to comment.

Given a table of moving vehicle locations, for each vehicle I wish to find the most recent recorded position (and other data about the vehicle at that time). Based on answers in the other question, I can run a query like:

Table definition:

CREATE TABLE VehiclePositions
(
    Id BIGINT NOT NULL,
    VehicleID  NVARCHAR(12) NULL,
    Timestamp DATETIME NULL,
    PositionX FLOAT NULL,
    PositionY FLOAT NULL,
    PositionZ SMALLINT NULL,
    Speed SMALLINT NULL,
    Heading SMALLINT NULL 
)

Query:

select *
from 
    (select 
         *,
         row_number() over (partition by VehicleID order by Timestamp desc) as ranking
     from VehiclePositions) as x
where 
    ranking = 1

Now, the problem is that this does a full table scan. I thought that by creating an appropriate index, I could avoid this:

CREATE INDEX idx_VehicPosition ON VehiclePositions(VehicleID, Timestamp);

However, SQL Server will happily ignore this index in the query and still perform the stable scan.

Note: I can get SQL Server to use the index, but the code is rather ugly:

DECLARE @ids TABLE (id NVARCHAR(12) UNIQUE)

INSERT INTO @ids 
    SELECT DISTINCT VehicleID 
    FROM VehiclePositions

SELECT ep.* 
FROM VehiclePositions vp
WHERE Timestamp = (SELECT Max(TimeStamp) FROM VehiclePositions vp2 
                   WHERE vp2.VehicleID = vp.VehicleID)
  AND VehicleID IN (SELECT DISTINCT id FROM @ids)

(The VehicleID IN... is because it seems SQL Server doesn't implement seek-skip optimisations. It still comes up with a pretty non-optimal query plan that visits the index twice, but at least it doesn't execute in linear time).

Is there a way to make SQL Server run the window function query intelligently?

I'm using SQL Server 2014...

Help will be appreciated

sapi · Accepted Answer · 2019-01-19 10:56:10Z

0

What i would do :

  SELECT *
  FROM
     (SELECT  MAX(Timestamp) as maxtime,
             VehicleID 
     FROM VehiclePositions
     GROUP BY VehicleID ) as maxed INNER JOIN 
    (SELECT  Id ,
             VehicleID ,
             Timestamp ,
             PositionX ,
             PositionY,
             PositionZ,
             Speed ,
             Heading 
     FROM VehiclePositions) as vals
       ON maxed.maxtime = vals.Timestamp
      AND maxed.VehicleID = vals.VehicleID

to my knowledge you cant get around your index getting scanned twice.

answered Jan 19, 2019 at 10:56

sapi

2441 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gert-Jan · Accepted Answer · 2019-01-20 12:27:03Z

0

As long as you are selecting all vehicles from the table and are select all column (or at least columns that are not in your index), I would expect the table scan to keep popping up.

In many cases, that will actually be the most efficient query plan. Only if you have a many rows per vehicle (like several pages) a seek strategy might be faster.

If you do have a lot of rows per vehicle, you might consider partitioning your table on Timestamp...

answered Jan 20, 2019 at 12:27

Gert-Jan

3471 silver badge9 bronze badges

Comments

CathyQian · Accepted Answer · 2020-06-26 17:46:29Z

0

You can filter results in windows function using 'qualify', as follows:

select *
from VehiclePositions
qualify row_number() over (partition by VehicleID order by Timestamp desc) = 1

answered Jun 26, 2020 at 17:46

CathyQian

1,16917 silver badges31 bronze badges

Collectives™ on Stack Overflow

Efficient limit result set in SQL window function

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related