May 2, 2016 at 9:22 am
I recently found, in an in-house product, a statement that suddenly started to perform very poorly (100 times slower) as our database grew, like if, passed a certain treshold, SQL Server changes its execution plan completely. Statement looks like:
SELECT TOP 1 MyColumn
FROM MyTable T
LEFT JOIN MyOtherTable O ON O.ID = T.LookupID
WHERE T.EntityID = @entityID AND O.IsPurged = 'F'
ORDER BY O.EffectiveDate
Removing the TOP makes the statement run fast again. It then typically returns one record but sometimes two or three. Rewriting the statement to:
SELECT TOP 1 MyColumn
FROM MyTable T
LEFT JOIN MyOtherTable O ON O.ID = T.LookupID AND O.IsPurged = 'F'
WHERE T.EntityID = @entityID
ORDER BY O.EffectiveDate
solves the problem (and I know that filtering within the LEFT JOIN is a better practice). Migrating from SQL 2008 to SQL 2014 does not help.
Besides revising every statement of the application (and there are a lot of them), is there something simpler that can be done?
May 2, 2016 at 9:33 am
The row goals introduced by things like TOP can sometimes cause incorrect estimates.
https://blogs.msdn.microsoft.com/bartd/2012/03/14/row-goals-gone-rogue/ is one good examination of that.
Also, with an outer join, the placement of the criterion for the inner table (WHERE clause or JOIN clause) isn't just a matter of good/bad practice; they're logically distinct queries that return different results.
If you could post the actual execution plans, we'll probably be able to point out why the slow query is as slow it is.
Cheers!
May 2, 2016 at 9:35 am
If "MyOtherTable" has an index on O.ID, add "IsPurged" to the key of that index.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply