Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nwoolls
Starting Member
3 Posts |
 Posted - 2010-03-23 : 12:40:41
|
| I have been working on migrating some of our data from Microsoft SQL Server 2000 to 2008. Among the usual hiccups and whatnot, I’ve run across something strange. Linked below is a SQL query that returns very quickly under 2000, but takes 20 minutes under 2008. I have read quite a bit on upgrading SQL server and went down the usual paths of checking indexes, statistics, etc. before coming to the conclusion that the following statement, found in the WHERE clause, causes the execution plan for the steps that follow this statement to change dramatically: And ( @bOnlyUnmatched = 0 -- offending line Or Not Exists(The SQL statements and execution plans are linked below.A coworker was able to rewrite a portion of the WHERE clause using a CASE statement, which seems to “trick” the optimizer into using a better execution plan. The version with the CASE statement is also contained in the linked archive.I’d like to see if someone has an explanation as to why this is happening and if there may be a more elegant solution than using a CASE statement. While we can work around this specific issue, I’d like to have a broader understanding of what is happening to ensure the rest of the migration is as painless as possible.Link: http://bit.ly/ceWzOaThanks in advance! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-23 : 13:26:59
|
| Have you tried to rewrite the query to be a join instead of a NOT EXISTS? I'm pretty sure that MS fixed the bit issue, but you could also try to CAST(0 AS BIT) before comparing to the variable. But, the main issue is that you can't (in general) depend of the order of evaluation. Especially, on SQL 2005 and above that use parallel execution plans. So, it may (and does in the case) execute the NOT EXISTS clause for each row. I suspect that a join will perform better that the query that doesn't work (since it doesn’t work not too hard to beat it). But, without knowing your data it's hard to say if it'd outperform the NOT EXITS without testing. I’d test both a JOIN and the NOT EXISTS clause and compare. If you have larger data sets I’d think the join will perform better. But, test, test, test! :) |
 |
|
|
nwoolls
Starting Member
3 Posts |
Posted - 2010-03-23 : 16:40:10
|
| Thanks for the suggestions - I'll try the join.I've also read different sources that indicate you can't rely on the order of execution. However, why would this cause the execution plan itself to change. If the bit evaluation is removed, the NOT EXISTS still runs, but the whole thing returns in a couple of seconds versus 20 minutes.One person suggested that what I'm seeing may be due to "parameter sniffing", but I've also tried using RECOMPILE hints and got the same query time and execution plan.Thanks again for the feedback! |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-03-23 : 19:56:09
|
| I'd try the OPTION (RECOMPILE) clause, too. This should cause the calls with "@bOnlyUnmatched = 0" to ignore the EXISTS predicate.=======================================There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980) |
|
|
|
nwoolls
Starting Member
3 Posts |
Posted - 2010-03-24 : 11:24:09
|
| Thanks for the response Bustaz - I've tried both of the following with the same results (bad execution plan):Option (Recompile)andOption (Optimize For (@bOnlyUnmatched = 1)) |
|
|
|
|
|
|
|
|
Bit Param Evaluation alters Execution Plan