Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT TOP is slow, regardless of ORDER BY

Tags:

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 final SELECT, 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! Using SET 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 before TOP, like it does when TOP 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, and mytable in the other database in the same MSSQL instance has a few million records. Both tables do have indexes on their respective sortcode column.

like image 872
richardtallent Avatar asked Jun 09 '11 00:06

richardtallent


People also ask

How does ORDER BY affect query performance?

Anytime you add ordering to a query it will affect performance because the results have to be ordered. The amount of impact however is NOT a static answer.

Is Select * slower than select column?

Selecting distinct and less than all columns will always be faster than selecting *.

How do I make my select statement faster?

Use column names instead of SELECT * While using SELECT statements use only the columns you need in your result, instead of using SELECT * from … This will reduce the result size considerably and speed your SQL query.

Is ORDER BY before select?

ORDER BY is evaluated before the SELECT, as the ordering changes the results returned.


1 Answers

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.

like image 130
Damien_The_Unbeliever Avatar answered Sep 22 '22 13:09

Damien_The_Unbeliever