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?

dbms name please – Zaynul Abadin Tuhin Apr 11 '19 at 11:57 — Zaynul Abadin Tuhin, Apr 11 '19 at 11:57
Do you have indices on `A.DateValue` and `A.ColumnValue`? – vahdet Apr 11 '19 at 11:59 — vahdet, Apr 11 '19 at 11:59

score 3 · Accepted Answer · answered Apr 11 '19 at 17:06

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

score 1 · Answer 2 · answered Apr 11 '19 at 11:59

1

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

answered Apr 11 '19 at 11:59

Zaynul Abadin Tuhin

31,407
5
33
63

2

@AlbertoMuñozSánchez then share the execution plan in question – Zaynul Abadin Tuhin Apr 11 '19 at 12:05

score 0 · Answer 3 · answered Apr 11 '19 at 12:35

0

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

answered Apr 11 '19 at 12:35

SHASHI SHEKHAR Barnwal

314
2
8

score 0 · Answer 4 · answered Apr 11 '19 at 12:58

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.

Very slow query with TOP and ORDER BY

4 Answers4