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
Last activity: Jul 5, 2022, 02:01 PM