"Top N Sort" with multiple "Order By" columns doesn't take advantage of an ordered source - by Ivan.Hamilton

Status : 

 


1
0
Sign in
to vote
ID 868599 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 5/6/2014 10:23:50 PM
Access Restriction Public

Description

In a simple case - a "TOP n" select statement with a single ORDER BY column, reads only the required number of rows from a suitably ordered source (index) to resolve the query.

When a second column, that is included (but not optimally ordered) in the source, is added to the query - the ordering of the source doesn't appear to be utilised. Resulting in far too many records being read to fulfill the query.

-- Create example table
CREATE TABLE [t]([id] [int] IDENTITY , [a] [int] NOT NULL , [b] [int] NOT NULL)
CREATE CLUSTERED INDEX [CI] ON [t] ([a] ASC)
-- Fill with some data.

--Query
SELECT TOP (1) b FROM t ORDER BY a, id
-- Result: Table 't'. Scan count 1, logical reads 312, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Query (split a & id ORDER BY, using WITH TIES on 'inner' queries)
SELECT TOP (1) b FROM
    (SELECT TOP (1) WITH TIES t.b , t.id FROM t ORDER BY a) st 
ORDER BY id
-- Result: Table 't'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Of course, this example relies on the idea that my two select statements above are logically equivalent.
Sign in to post a comment.