SQL Server Performance Forum – Threads Archive

Select top n slower than select all

Hello, I’m building a ASP.NET application. On one of the page the query result may return thousands of records. To make it presentable, I put custom paging in the page. However if I continue to use the query, it still returns all 2000 to 5000 records. When I change the query to "Select top n", it runs even slower, takes 45 to 60 seconds instead of 10 to 15 seconds. Is this normal? Am I doing something wrong? More info: Here’s my "Select top 20" query: select top 20 lm.ResidentZip as ZipCode, count(lm.Residentzip) as LeadsCountInZip
from tblLeadsMaster lm
where not exists (select null from tblOrderDetails od where od.InstitutionNumber = lm.InstitutionNumber and od.LoanNumber = lm.LoanNumber)
and lm.AgentNumber = @AgentNumber
group by lm.ResidentZip
order by lm.Residentzip The table "tblLeadsMaster has about 4 Million records, it has index on ResidentZip and AgentNumber column and primary key on "InstitutionNumber and LoanNumber". Any pointers will be greatly appreciated! ficisa

---

It runs slower because it’s having to get your entire statement, order it, then chop off the first 20 records. You need to change the NOT EXISTS to a left outer join. Also, do you have indexes on InstitutionNumber, LoanNumber, AgentNumber, and ResidentZip? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

So Left outer join is actually faster than "Not Exist"? I thought table join actually affects the performance of the query.

---

It’s not always. It should be faster in your example though because of how you evaluate the select null. The biggest reason it’s slower then all though is because you have to order by. To order by and get the top 20, it has to evaluate everything first. Make sense? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>