Why select Top clause could lead to long time cost

Question

The following query takes forever to finish. But if I remove the top 10 clause, it finishs rather quickly. big_table_1 and big_table_2 are 2 tables with 10^5 records.

I used to believe that top clause will reduce the time cost, but it's apparently not here. Why???

select top 10 ServiceRequestID
from 
(
    (select * 
     from  big_table_1
     where big_table_1.StatusId=2
    ) cap1
    inner join
      big_table_2 cap2
    on cap1.ServiceRequestID = cap2.CustomerReferenceNumber
    )

Should the index have influence on this? I don't think there's sorting. — smwikipedia, Mar 08 '12 at 11:49
@Andrzej, I added that and remove the top 10, still very slow. — smwikipedia, Mar 08 '12 at 11:54
Not an answer to your question, but the query should run much faster as `SELECT TOP 10 ServiceRequestId FROM big_table_1 cap1 JOIN big_table_2 cap2 ON cap1.ServiceRequestID = cap2.CustomerReferenceNumber WHERE cap1.StatusId=2;` — Joachim Isaksson, Mar 08 '12 at 12:00
@JoachimIsaksson, that's exactly what i expected, but the fact is a bitch. — smwikipedia, Mar 08 '12 at 12:04
@smwikipedia Note that I rewrote your nested selects as a single join, that's what would speed it up. — Joachim Isaksson, Mar 08 '12 at 12:11
@Joachim I tested smwikipedia's approach with my database, it worked quite fast, there must be some indexing problems (although my tables were smaller - 85k rows and 3k rows, but still it worked fast), I used postgresql 8.3 — Andrzej Bobak, Mar 08 '12 at 12:21
@Shark The query plan is quite a big XML file. How to upload it here? — smwikipedia, Mar 12 '12 at 02:12
I don't think you can upload it here but you can put it somewhere like Dropbox and provide a public URL for it, that's what I've seen in the past. I'm surprised the plan is that long though considering the query isn't that complex — Matt Dodge, Mar 12 '12 at 03:22
Please: `SQL Server` and `SQL` are NOT the same thing. To make your question and people's answers more useful to others, do specify which DBMS you are talking about. — cornuz, Mar 12 '12 at 14:04
Thanks for reminding. I have updated the question with DBMS. — smwikipedia, Mar 13 '12 at 02:30
Any update on the execution plans? You can paste the XML up to pastebin or gist. Without that you will just get speculation and possible reasons. — Martin Smith, Mar 14 '12 at 20:12

score 13 · Answer 1 · edited May 23 '17 at 11:54

There are other stackoverflow discussions on this same topic (links at bottom). As noted in the comments above it might have something to do with indexes and the optimizer getting confused and using the wrong one.

My first thought is that you are doing a select top serviceid from (select *....) and the optimizer may have difficulty pushing the query down to the inner queries and making using of the index.

Consider rewriting it as

select top 10 ServiceRequestID  
from  big_table_1
inner join big_table_2 cap2
on cap1.servicerequestid = cap2.customerreferencenumber
and big_table_1.statusid = 2

In your query, the database is probably trying to merge the results and return them and THEN limit it to the top 10 in the outer query. In the above query the database will only have to gather the first 10 results as results are being merged, saving loads of time. And if servicerequestID is indexed, it will be sure to use it. In your example, the query is looking for the servicerequestid column in a result set that has already been returned in a virtual, unindexed format.

Hope that makes sense. While hypothetically the optimizer is supposed to take whatever format we put SQL in and figure out the best way to return values every time, the truth is that the way we put our SQL together can really impact the order in which certain steps are done on the DB.

SELECT TOP is slow, regardless of ORDER BY

Why is doing a top(1) on an indexed column in SQL Server slow?

If you have a column A with an index IX_COL_A then adding the following to the query might improve the speed: Select * from MYTABLE WITH (NOLOCK, index(IX_COL_A)) ... WHERE ... — Andrei Sura, Sep 27 '16 at 17:45

score 7 · Answer 2 · answered Dec 20 '13 at 14:27

7

I had a similar problem with a query like yours. The query ordered but without the top clause took 1 sec, same query with top 3 took 1 minute.

I saw that using a variable for the top it worked as expected.

The code for your case:

declare @top int = 10;

select top (@top) ServiceRequestID
from 
(
    (select * 
     from  big_table_1
     where big_table_1.StatusId=2
    ) cap1
    inner join
      big_table_2 cap2
    on cap1.ServiceRequestID = cap2.CustomerReferenceNumber
    )

answered Dec 20 '13 at 14:27

Javier Suero Santos

544
7
21

Yes. You are Right. This a terrible bug, declaring the variable works, but that does not make any sense at all. – André Mendonça Jun 15 '19 at 21:32
This works, I was pulling my hair trying to figure out why SELECT TOP 6311 was taking < 1 second but SELECT TOP 6271 was timing out. Thank you! – DoritoBandito Apr 23 '20 at 04:08

score 4 · Answer 3 · answered Mar 12 '12 at 13:55

I cant explain why but I can give an idea:

try adding SET ROWCOUNT 10 before your query. It helped me in some cases. Bear in mind that this is a scope setting so you have to set it back to its original value after running your query.

Explanation: SET ROWCOUNT: Causes SQL Server to stop processing the query after the specified number of rows are returned.

score 4 · Answer 4 · answered Mar 13 '12 at 00:06

This can also depend on what you mean by "finished". If "finished" means you start seeing some display on a gui, that does not necessarily mean the query has completed executing. It can mean that the results are beginning to stream in, not that the streaming is complete. When you wrap this into a subquery, the outer query can't really do it's processing until all the results of the inner query are available:

the outer query is dependent on the length of time it takes to return the last row of the inner query before it can "finish"
running the inner query independently may only requires waiting until the first row is returned before seeing any results

In Oracle, there were "first_rows" and "all_rows" hints that were somewhat related to manipulating this kind of behaviour. AskTom discussion.

If the inner query takes a long time between generating the first row and generating the last row, then this could be an indicator of what is going on. As part of the investigation, I would take the inner query and modify it to have a grouping function (or an ordering) to force processing all rows before a result can be returned. I would use this as a measure of how long the inner query really takes for comparison to the time in the outer query takes.

Drifting off topic a bit, it might be interesting to try simulating something like this in Oracle: create a Pipelined function to stream back numbers; stream back a few (say 15), then spin for a while before streaming back more.

Used a jdbc client to executeQuery against the pipelined function. The Oracle Statement fetchSize is 10 by default. Loop and print the results with a timestamp. See if the results stagger. I could not test this with Postgresql (RETURN NEXT), since Postgres does not stream the results from the function.

Oracle Pipelined Function

A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)

Postgresql RETURN NEXT

Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation.

JDBC Default Fetch Sizes

statement.setFetchSize(100);

while the requester is specifically talking about sql server, and oracle handles "where rownum < ..." differently than SQL Server handles "top n", (and also subqueries are optimized differently between the two), +1 for some solid information. — user158017, Mar 14 '12 at 00:36
@sql_mommy Thanks. I wasn't sure of the rdbms type earlier. But I remember puzzling through this kind of scenario in Oracle a few years back. Just wrapping the inner query with a "select count(*) from ()" should give an indication of the true length of time the inner query take. If it is fast, then this idea can be discarded. — Glenn, Mar 14 '12 at 12:49

score 2 · Answer 5 · answered Jun 17 '16 at 20:36

I've just had to investigate a very similar issue.

SELECT TOP 5 *
FROM t1 JOIN t2 ON t2.t1id = t1.id 
WHERE t1.Code = 'MyCode' 
ORDER BY t2.id DESC

t1 has 100K rows, t2 20M rows, The average number of rows from the joined tables for a t1.Code is about 35K. The actual resultset is only 3 rows because t1.Code = 'MyCode' only matches 2 rows which only have 3 corresponding rows in t2. Stats are up-to-date.

With the TOP 5 as above the query takes minutes, with the TOP 5 removed the query returns immediately.

The plans with and without the TOP are completely different.

The plan without the TOP uses an index seek on t1.Code, finds 2 rows, then nested loop joins 3 rows via an index seek on t2. Very quick.
The plan with the TOP uses an index scan on t2 giving 20M rows, then nested loop joins 2 rows via an index seek on t1.Code, then applies the top operator.

What I think makes my TOP plan so bad is that the rows being picked from t1 and t2 are some of the newest rows (largest values for t1.id and t2.id). The query optimiser has assumed that picking the first 5 rows from an evenly distributed average resultset will be quicker than the non-TOP approach. I tested this theory by using a t1.code from the very earliest rows and the response is sub-second using the same plan.

So the conclusion, in my case at least, is that the problem is a result of uneven data distribution that is not reflected in the stats.

score 2 · Answer 6 · answered Mar 16 '12 at 02:42

When debugging things like this I find that the quickest way to figure out how SQL Server "sees" the two queries is to look at their query plans. Hit CTRL-L in SSMS in the query view and the results will show what logic it will use to build your results when the query is actually executed.

SQL Server maintains statistics about the data your tables, e.g. histograms of the number of rows with data in certain ranges. It gathers and uses these statistics to try to predict the "best" way to run queries against those tables. For example, it might have data that suggests for some inputs a particular subquery might be expected to return 1M rows, while for other inputs the same subquery might return 1000 rows. This can lead it to choose different strategies for building the results, say using a table scan (exhaustively search the table) instead of an index seek (jump right to the desired data). If the statistics don't adequately represent the data, the "wrong" strategy can be chosen, with results similar to what you're experiencing. I don't know if that's the problem here, but that's the kind of thing I would look for.

score 2 · Answer 7 · answered Mar 17 '12 at 14:14

If you want to compare performances of your two queries, you have to run these two queries in the same situation ( with clean memory buffers ) and have mumeric statistics

Run this batch for each query to compare execution time and statistics results (Do not run it on a production environment) :

DBCC FREEPROCCACHE
GO

CHECKPOINT 
GO

DBCC DROPCLEANBUFFERS 
GO

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

-- your query here
GO

SET STATISTICS TIME OFF
GO

SET STATISTICS IO OFF
GO

score 1 · Answer 8 · answered Mar 14 '12 at 09:27

TOP does not sort the results to my knowledge unless you use order by.

So my guess would be, as someone had already suggested, that the query isn't taking longer to execute. You simply start seeing the results faster when you don't have TOP in the query.

Try using @sql_mommy query, but make sure you have the following:

To get your query to run faster, you could create an index on servicerequestid and statusid in big_table_1 and an index on customerreferencenumber in big_table_2. If you create unclustered indexes, you should get an index only plan with very fast results.

If I remember correctly, the TOP results will be in the same order as the index you us on big_table_1, but I'm not sure.

Gísli

true - Top does NOT sort unless you use order by. And that will slow down the results, because it returns everything, sorts, and then grabs the top n. Top n with no order by can be very fast, because it starts merging the data and stops at n to return the data. Also, wish I could give a second upvote, because index only plans are always the best! — user158017, Mar 14 '12 at 17:51

score 0 · Answer 9 · answered Mar 18 '12 at 00:17

It might be a good idea to compare the execution plans between the two. Your statistics might be out of date. If you see a difference between the actual execution plans, there is your difference in performance.

In most cases, you would expect better performance in the top 10. In your case, performance is worse. If this is the case you will not only see a difference between the execution plans, but you will also see a difference in the number of returned rows in the estimated execution plan and the actual execution plan, leading to the poor decission by the SQL engine.

Try again after recomputing your statistics (and while you're at it, rebuilding indices)

Also check if it helps to take out the where big_table_1.StatusId=2 and instead go for

select top 10 ServiceRequestID
from  big_table_1 as cap1 INNER JOIN
big_table_2 as cap2
ON cap1.ServiceRequestID = cap2.CustomerReferenceNumber
WHERE cap1.StatusId=2

I find this format much more readable, though it should (though remotely possibly it doesn't) optimise to the same execution plan. The returned endresult will be identical regardless

Why select Top clause could lead to long time cost

9 Answers9

Linked

Related