Very slow query with TOP and ORDER BY

Question

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?

Answer 1

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