Bhuvnesh (1/9/2013)
julian.fletcher (1/9/2013)
Really? Do you have any links to further details? That would be very helpful.sse this link http://sqlserverplanet.com/optimization/using-union-instead-of-or
That's what I was talking about with SQL 2000 and prior optimiser limitations. It's for OR in a where clause (and to be honest, it's far less relevant since SQL 2005), not OR in an IF.
Oh, and as for those examples he gave in that blog post...
The one with the OR:
Table 'SalesOrderDetail'. Scan count 5, logical reads 10564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 337 ms.
The one with the Union:
Table 'SalesOrderDetail'. Scan count 10, logical reads 19068, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 323 ms.
So the 'efficient' version with the UNION uses 60ms more CPU time and does 9000 more logical reads than the 'inefficient' version with the OR. Hmmmm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability