Home Dashboard Directory Help
Search

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


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 868599
Opened: 5/6/2014 10:23:50 PM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
Sign in to post a comment.
Sign in to post a workaround.
Posted by Ivan.Hamilton on 5/6/2014 at 10:25 PM
Split ORDER BY into sub-selects

--Query
SELECT TOP (1) b FROM t ORDER BY a, id

--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