April 26, 2011 at 8:40 pm
Hi all - I'm wondering which query is better - If Not Exists vs a variable with an If statement.
Option 1:
IF NOT EXISTS
(
SELECT (1)
FROM MyTable a
,MyOtherTable b
WHERE a.MyType = b.MyType
AND MyStatus = 'A'
AND a.UserID = 1
)
BEGIN
Print 'My Message.'
END
Option 2:
Declare @MyVariable int
SELECT @MyVariable = CredType
FROM MyTable a
,MyOtherTable b
WHERE a.MyType = b.MyType
AND MyStatus = 'A'
AND a.UserID = 1
If @MyVariable is NULL or @MyVariable = 0
BEGIN
Print 'My Message.'
END
I thought the second one would be faster, but my trace is showing more reads in option2 - maybe I just don't have enough test data? Any thoughts?
April 27, 2011 at 12:20 am
In Option 2 , as its an assignment-to-variable operation, the query has to scan ( or seek ) through the entire table and will assign the last value it retrieved to that variable. So reads are high.
For Option 1 , Optimizer got a row which matches your criteria, so it breaks the select operation in between and prints your message, leaving a lesser Read count.
I am not 100% sure on Option 1 explanation that Optimizer will break the select as soon as it fetches a matching row, so lets for our MVPs to throw in their cents 🙂
Hope that helps 🙂
April 27, 2011 at 2:38 pm
Yup, ColdCoffee, you are right! EXISTS will stop searching as soon as it finds the first row. Other command will set the variable each time it finds a resulting row, until all rows are processed, leaving the last one's value stored in the variable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply