Sample Header Ad - 728x90

Adding 'top 20' massively slows down query!

2 votes
1 answer
1132 views
I have a query: select * from Aview where field=20 order by id desc This returns 2700 rows from the view in about 1 second. Adding 'top 20' to the query makes MSSQL return in 43 seconds!! This has been a HARD to reproduce issue, and doing a rebuild of statistics fixes the issue for a couple of days, but then it come back. I've been working with SQL for decades and I've never once seen a situation where adding a 'top' causes the time to increase. Looking at the execution plan, it seems to be doing a lazy spool of 960 MILLION rows if you do the top 20, but not if you don't.
Asked by Traderhut Games (173 rep)
Jun 29, 2022, 03:58 PM
Last activity: Jul 5, 2022, 02:01 PM