SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ISNULL(X,Y) or ISNULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robowski
Posting Yak Master

101 Posts

Posted - 02/18/2014 :  07:05:27  Show Profile  Reply with Quote
Just wondering if what would be best for performance.

Gender field in this case will only hold 2 values, Female and Male and in some cases a NULL mark.

If you are creating a dataset only to return people who are female or have a null gender would it be best for performance to use:

Ignore the 1=1, for development use only and wont be used in production code.

Gender column isn't indexed

WHERE 1=1
AND ISNULL(Gender, 'Female') = 'Female'

or

WHERE 1=1
AND (Gender = 'Female' OR Gender IS NULL)

Also, what would the best way of testing this be? just looking at the execution plan?

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  11:01:54  Show Profile  Reply with Quote
I'm glad you asked out to test this yourself - that is a good sign :)

I usually compare the execution plans (if there's no difference then it doesn't matter which you use - for that statement).
And I also compare output of the statement with these SETs:

set statistics io on --physical and logical reads
set statistics time on --execution time by statement in milliseconds

Let us know what you find


Be One with the Optimizer
TG
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 02/18/2014 :  11:33:33  Show Profile  Reply with Quote
quote:
Originally posted by TG

I'm glad you asked out to test this yourself - that is a good sign :)

I usually compare the execution plans (if there's no difference then it doesn't matter which you use - for that statement).
And I also compare output of the statement with these SETs:

set statistics io on --physical and logical reads
set statistics time on --execution time by statement in milliseconds

Let us know what you find


Be One with the Optimizer
TG



Cheers,

usually use thing liks showplan xml, and the execution plans but was wondering if there were any further ways of doing. Can be a bit tricky when dealing with nearly a billion rows! also cautious of the fact the first execution plan may be cached and the following partially using that and five a slightly false performance boost.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000