ISNULL performance question
-
I'm building procs that have 2 optional parameters for accessing data (query by id or guid).
My question is how the where clause is best defined.
I can see 2 ways of building it:
A) WHERE RecordId = ISNULL(@RecordID, RecordID) AND
RecordGuid = ISNULL(@RecordGuid, RecordGuid)
B) WHERE (RecordId = @RecordId OR @RecordId IS NULL)
AND (RecordGuid = @RecordGuid OR @RecordGuid IS NULL)
AND (RecordId IS NOT NULL OR RecordGuid IS NOT NULL)
I like the first one because it's nice and concise, but I'm concerned about the performance of it. I was wondering if anyone had any feedback regarding the performance of an isnull like this.
Thanks.
-
I would have two queries.
If @RecordId IS NULL
... WHERE RecordGuid=@RecordGuid
ELSE
... WHERE RecordId=@RecordId
Of course, this assume that exactly one will always be provided.
Brian
-
I'm trying to do this without 2 queries, because that means 2 places to maintain return columns. While I don't want to ever assume exactly one will always be provided, that's how it's designed. I'd like it to cover as many contingency plans as I can without going overboard, but at some point you have to accept that receiving bad data will return a bad result.
-
Then how about
SELECT ....
WHERE CASE WHEN @RecordId IS NULL THEN RecordGuid ELSE RecordId END=CASE WHEN @RecordId IS NULL THEN @RecordGuid ELSE @RecordId END
AND CASE WHEN @RecordGuid IS NULL THEN RecordId ELSE RecordGuid END=CASE WHEN @RecordGuid IS NULL THEN @RecordId ELSE @RecordGuid END
This should work even if both values are passed. If both are passed, it will make sure both values point to the same record.
Brian
-
Is there an advantage to your suggestion over either of mine? You're taking 2 conditions and turning them into 2 sets of case statements, where each set contains the same condition repeated. I don't see any way that your suggestion would be more efficient than my B plan, they both use {IS|IS NOT} NULL. Is using IS NULL that much more efficient than IS NOT NULL? Is either one more efficient than the ISNULL statement that I had suggested first? I appreciate the suggestions, but I've seen no reason why one statement would be any better than the others.
-
W/o actually testing it I can say that "IS NULL" is better then "IS NOT NULL" although not completely sargable. Run the execution plan on the various versions of your query and see which one has the least amount of costly items (eg. table scans, clustered index scans). So far I would venture to guess the suggestion of 2 separate queries will be the fastest. Although you will have more code to maintain I would say that since you will only be testing one condition in the where clause it is going to be faster.
HTH...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
On my system, both of your options cause a clustered index scan. Ideally you want an index seek. This is accomplished by my first suggestion. Based on your table definition and size, you may see different results. I suggest you Show Execution Plan and run some tests yourself.
Brian
-
Option B) is preferrable.
But it may be done in shorter way:
WHERE (RecordId = @RecordId OR RecordGuid = @RecordGuid)
If both @RecordId and @RecordGuid are NULL no rows will be returned.
_____________
Code for TallyGenerator -
... and it will allow index SEEKs
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, I like that suggestion. It looks like I was overthinking myself on my B option.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply