Alex Rivera | Logout

Why select Top clause could lead to long time cost

Asked 2012-03-08T11:30:52.667
21

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
    )
sql sql-server sql-server-2008-r2
Edit
Report

2 Answers

4

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

answered 2012-03-13T00:06:28.857
1

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

answered 2012-03-14T09:27:53.210

Your Answer