SELECT TOP is slow, regardless of ORDER BY
And so starts the unfortunate game of "trying to outsmart the optimizer (because it doesn't always know best)".
You can try putting the filtering portions into a subquery or CTE:
SELECT TOP 30 *
FROM
(SELECT *
FROM myview, foo, bar
WHERE shared=1 AND [joins and other stuff]) t
ORDER BY sortcode;
Which may be enough to force it to filter first (but the optimizer gets "smarter" with each release, and can sometimes see through such shenanigans). Or you might have to go as far as putting this code into a UDF. If you write the UDF as a multistatement table-valued function, with the filtering inside, and then query that UDF with your TOP x
/ORDER BY
, you've pretty well forced the querying order (because SQL Server is currently unable to optimize around multistatement UDFs).
Of course, thinking about it, introducing the UDF is just a way of hiding what we're really doing - create a temp table, use one query to populate it (based on WHERE filters), then another query to find the TOP x
from the temp table.
Related videos on Youtube
Comments
-
Fear605 about 4 years
I have a fairly complex query in SQL Server running against a view, in the form:
SELECT * FROM myview, foo, bar WHERE shared=1 AND [joins and other stuff] ORDER BY sortcode;
The query plan as shown above shows a
Sort
operation just before the finalSELECT
, which is what I would expect. There are only 35 matching records, and the query takes well under 2 seconds.But if I add
TOP 30
, the query takes almost 3 minutes! UsingSET ROWCOUNT
is just as slow.Looking at the query plan, it now appears to sort all 2+ million records in
myview
before the joins and filters.This "sorting" is shown on the query plan as an Index Scan on the
sortcode
index, a Clustered Index Seek on the main table, and a Nested Loop between them, all before the joins and filters.How can I force SQL Server to
SORT
just beforeTOP
, like it does whenTOP
isn't specified?I don't think the construction of
myview
is the issue, but just in case, it is something like this:CREATE VIEW myview AS SELECT columns..., sortcode, 0 as shared FROM mytable UNION ALL SELECT columns..., sortcode, 1 as shared FROM [anotherdb].dbo.mytable
The local
mytable
has a few thousand records, andmytable
in the other database in the same MSSQL instance has a few million records. Both tables do have indexes on their respectivesortcode
column.-
ypercubeᵀᴹ about 13 yearsSo, the
WHERE shared=1
in your query actually cancels the first part of theUNION
view? -
Fear605 about 13 years@ypercube, yes, in the case of this particular query.
-
ypercubeᵀᴹ about 13 yearsIt wouldn't hurt if you posted the whole
[joins and other stuff]
part of the query. Perhaps some other index missing is what causes the optimizer to take the slow path.
-
-
Jacob van Lingen almost 6 yearsTip: Do include the
ORDER BY
otherwise this trick won't work!