Attempting to replace not in with left outer join

Question

Attempting to replace not in with left outer join

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

jackimo SSCommitted Points: 1885 More actions · Answer 1

So interesting fact ....

All along I've never considered that the statistics would be out of date, because we have a job that updates stats that runs daily. And of course now I notice that the job has been failing for some time now. So the simple solution is sometimes the right solution.

Thanks for all the assistance!

sql-lover SSCoach Points: 18530 More actions · Answer 2

jackimo (4/16/2015)
So interesting fact ....
All along I've never considered that the statistics would be out of date, because we have a job that updates stats that runs daily. And of course now I notice that the job has been failing for some time now. So the simple solution is sometimes the right solution.
Thanks for all the assistance!

Just curious ... how are you refreshing those stats? I would recommend Ola Halleegren solution. It does help you on that and defrag your Indexes.

But one point that I would like to make, regardless of how you are refreshing those, is be sure you're not refreshing them too frequently. In other words ... use a "smart logic" so your stats will be updated only if needed. You may be wasting CPU resources if you're doing it on all tables, every day.

jackimo SSCommitted Points: 1885 More actions · Answer 3

Thanks for the suggestion - I actually use Ola Hallengren's script for all the hosts I control. This one in particular is more under the supervision of a vendor. Great suggestion though.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

then I tried it with temp tables (two temp tables then the join) and boom, total execution goes way way down.
not sure what is going on here but I guess I should be happy with the improvement.

Maybe the temp database is on a faster drive? Plus it likely makes your code more readable as well to the next person that may have to maintain it. And it makes it easier to slip in comments to tag what each temp table is.

----------------------------------------------------

MadAdmin SSChampion Points: 11261 More actions · Answer 5

MMartin1 (4/17/2015)
then I tried it with temp tables (two temp tables then the join) and boom, total execution goes way way down.
not sure what is going on here but I guess I should be happy with the improvement.
Maybe the temp database is on a faster drive? Plus it likely makes your code more readable as well to the next person that may have to maintain it. And it makes it easier to slip in comments to tag what each temp table is.

If temp tables make it faster, then following from Gail's always on the money suggestions (i.e. out of date stats), Probably the temp tables have up to date stats since they were created in the query.

This thread has already been answered by Gail when she blamed stats due to a query hint outperforming the optimiser.

I move that the question creator mark this one as answered by Gail.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]