Top N Statements - Performance Considerations. 1

[SinThetik]

Hi guys.
Got a weird one I'm trying to work out...
Selecting top N from a view which has thousands of applicable records.

If I top 700 I get results in 4 seconds, if I top 750+ they never return - So I can't even get a query plan back to see what's different between the 2.

My "where" clause allows me to step up through 1000's upon 1000's of rows so it's not like I'm running out of records and forcing it to scan tables either.

Any ideas? Been at it all day and nothing really useful on Top N Queries comes back.

Using a Set Rowcount is even worse as it forces the whole result set to be loaded before sorting (In this case, it also never comes back).

Thanks!!

 

[vongrunt]

This also depends on JOINs and ORDER BY clause... In general, for smaller N optimizer attempts to materialize records through joins (nested loops) then use TOP operator. For larger N things can "suddenly" degrade to hash match/merge join/lazy spool + sort/topN operator.

 

[SinThetik]

Hey vongrunt,

Thanks for the advise!! My query finally came back and the execution plan clearly shows the intorduction of the lazy writer and a massively different plan altogether. I'll work on your suggestions and see what comes up!

Thanks!

 

[naggy]

Hi,
I am having the same kind og problem if i increment the records for TOP 50 to Top 100. The first top 50 takes 4 sec to return data, and the changing the same query to return TOP 100 changes take about two mins.