Query runs faster with ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query runs faster with ORDER BY

Author

Topic

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-01-29 : 09:33:47

Here's a fun one: a simple query that runs faster with an ORDER BY clause than without one. Shouldn't happen, should it?

Here's the set up:


CREATE TABLE RowNumberTest (
  k1 int NOT NULL,
  k2 int NOT NULL,
  v varchar(1000),
  PRIMARY KEY CLUSTERED (k1 ASC, k2 ASC)
)

SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @k1 int, @k2 int
SET @k1 = 1
WHILE @k1 <= 1000
BEGIN
    SET @k2 = 1
    WHILE 1 = 1
    BEGIN
        INSERT INTO RowNumberTest SELECT @k1, @k2, REPLICATE('a', CAST(FLOOR(RAND()*1000) AS int))
        SET @k2 = @k2 + 1
        IF RAND() < 0.02 BREAK
    END
    SET @k1 = @k1 + 1
END
COMMIT TRANSACTION
SET NOCOUNT OFF

SELECT COUNT(*) AS "row count"
FROM RowNumberTest

--SELECT ct, COUNT(*) AS occurs
--FROM (
--    SELECT k1, COUNT(*) AS ct
--    FROM RowNumberTest
--    GROUP BY k1
--  ) AS A
--GROUP BY ct
--ORDER BY ct
GO

SET SHOWPLAN_TEXT ON
GO

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 ASC) AS r
FROM dbo.RowNumberTest

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
ORDER BY k1 DESC, k2 DESC
GO

SET SHOWPLAN_TEXT OFF
GO

--DROP TABLE RowNumberTest

And here's the result (SQL Server 2005 sp2, Standard Edition):


row count
-----------
49867

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 ASC) AS r
FROM dbo.RowNumberTest

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]), ORDERED FORWARD)

(4 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Parallelism(Gather Streams, ORDER BY:([tempdb].[dbo].[RowNumberTest].[k1] ASC, [tempdb].[dbo].[RowNumberTest].[k2] DESC))
                      |--Sort(ORDER BY:([tempdb].[dbo].[RowNumberTest].[k1] ASC, [tempdb].[dbo].[RowNumberTest].[k2] DESC))
                           |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]))

(6 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
ORDER BY k1 DESC, k2 DESC

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]), ORDERED BACKWARD)

(4 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 09:37:36

With the use of Windowed functions it might be faster, since they internally sort things.
Post back the result of

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
ORDER BY k1, k2 DESC

The ORDER BY has same sorting directions as the ROW_NUMBER.
And to get rid of the parallellism, use OPTION (MAXDOP 1) at the end of the query.

E 12°55'05.25"
N 56°04'39.16"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-01-29 : 09:52:11

You're right, it would be a bit clearer without the parallelism.

Oh, I'd expect the plan for a query with ORDER BY k1, k2 DESC to contain the sort since the output order is different from both forward and backward traverse of the index. And it does.

The point is that without any order by it still does the sort even though it can be induced not to by including ORDER BY k1 DESC, k2 DESC. Adding the ordering constraint is forcing the optimizer to find a valid plan that it would otherwise miss. Which is fair enough, but it just seemed to me like an excessively simple query for that sort of thing to happen.


SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 ASC) AS r
FROM dbo.RowNumberTest
OPTION (MAXDOP 1)

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]), ORDERED FORWARD)

(4 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
OPTION (MAXDOP 1)

(1 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Sort(ORDER BY:([tempdb].[dbo].[RowNumberTest].[k1] ASC, [tempdb].[dbo].[RowNumberTest].[k2] DESC))
                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]))

(5 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
ORDER BY k1 DESC, k2 DESC
OPTION (MAXDOP 1)

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]), ORDERED BACKWARD)

(4 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT *, ROW_NUMBER() OVER (PARTITION BY k1 ORDER BY k2 DESC) AS r
FROM dbo.RowNumberTest
ORDER BY k1 ASC, k2 DESC
OPTION (MAXDOP 1)

--SET STATISTICS IO OFF
--SET STATISTICS TIME OFF

(1 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------
  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
            |--Segment
                 |--Sort(ORDER BY:([tempdb].[dbo].[RowNumberTest].[k1] ASC, [tempdb].[dbo].[RowNumberTest].[k2] DESC))
                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[RowNumberTest].[PK__RowNumberTest__1B0B69D3]))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources