CODEWITHSUNDEEP
sqlsql-servert-sqlinner-jointop-n
Alberto Muñoz Sánchez
Alberto Muñoz Sánchez

Reputation: 167

Very slow query with TOP and ORDER BY

I have a query in SQL Server 2014 that takes a lot of time to get the results when I execute it.

When I remove the TOPor the ORDER BYintructions, it executes faster, but if I write both of them, it takes a lot of time.

SELECT TOP (10) A.ColumnValue AS ValueA
FROM TableA AS A
    INNER JOIN TableB AS B
        ON A.ID = B.ID
WHERE A.DateValue > '1982-05-02'
ORDER BY ValueA

How could I make it faster?

Upvotes: 1

Views: 1222

Answers (4)

Martin Smith
Martin Smith

Reputation: 453067

You say

When I remove the TOP or the ORDER BY ... it executes faster

Which would indicate that SQL Server has no problem generating the entire result set in the desired order. It just goes pear shaped with the limiting of TOP 10. This is a common issue with rowgoals. When SQL Server knows you just need the first few results it can choose a different plan attempting to optimise for this case that can backfire.

More recent versions include the hint DISABLE_OPTIMIZER_ROWGOAL to disable this on a per query basis. On older versions you can use QUERYTRACEON 4138 as below.

SELECT TOP (10) A.ColumnValue AS ValueA
FROM TableA AS A
    INNER JOIN TableB AS B
        ON A.ID = B.ID
WHERE A.DateValue > '1982-05-02'
ORDER BY ValueA
OPTION (QUERYTRACEON 4138)

You can use this to verify the cause but may find permissions to run QUERYTRACEON are a problem.

In that eventuality you can hide the TOP value in a variable as below

DECLARE @Top INT = 10

SELECT TOP (@Top) A.ColumnValue AS ValueA
FROM TableA AS A
    INNER JOIN TableB AS B
        ON A.ID = B.ID
WHERE A.DateValue > '1982-05-02'
ORDER BY ValueA
option (optimize for (@Top = 1000000))

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is pretty hopeless, unless most of your data has an earlier date. If the date is special, you could create a computed persisted column to speed up the query in general. However, I doubt that is the case.

I can envision a better execution plan for the query phrased this way:

SELECT TOP (10) A.ColumnValue AS ValueA
FROM TableA A
WHERE EXISTS (SELECT 1 FROM TableB b WHERE A.ID = B.ID) AND
      A.DateValue > '1982-05-02'
ORDER BY ValueA;

with an indexes on TableA(ValueA, DateValue, Id, ColumnValue) and TableB(id). That execution plan would scan the index from the beginning and then do the test on DateValue and Id and return ColumnValue for the corresponding matching rows.

However, I don't think SQL Server would generate this plan (although it is worth a try), and I don't know how to force it if it doesn't.

Upvotes: 0

SHASHI SHEKHAR Barnwal
SHASHI SHEKHAR Barnwal

Reputation: 344

The best way would be to use the indexes to improve performance. Here, in this case, the index can be put on (date_value). For uses of indexes refer to this URL:using indexes

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

create the index based on ID column of both tables

    CREATE INDEX index_nameA
    ON TableA (ID, DateValue)
;
    CREATE INDEX index_nameB
    ON TableB (ID)

it will create better plan in times of query execution

Upvotes: 1

Related Questions