?
Solved

Performance hit using CASE STATEMENTS in the WHERE clause?

Posted on 2005-04-22
17
Medium Priority
?
308 Views
Last Modified: 2010-03-19
Does anybody have any idea or documentation of whether using CASE STATEMENTS in the WHERE part of the stored procedure is better or worse than other options.  In other words, which of these would be most effective:


WHERE
(ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')

or

WHERE
[OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN @officeLocation ELSE [OFFICE_LOCATION] END

Thanks!
0
Comment
Question by:mbosch
  • 5
  • 4
  • 2
  • +3
16 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13843556
I'm not sure of the performance hit but in the above case I don't see any reason for a CASE statement.  Your first solution is sufficient and in my opinion easier to follow.
0
 
LVL 1

Expert Comment

by:Mannaraprayil
ID: 13843584
Hi

I dont think the above two queries will return the same result set.
Please let us know what exactly you want to retrieve.

Again regarding the usage of CASE statements.
if you could get the results without using CASE statements it will be well and good.

Thanks
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13843600
The where clause are not logically equivalent.

In the first an empty string value for @officeLocation will mean the field OFFICE_LOCATION will be totally ignored.

In the second a record when @officeLocation is empty string will only select records where OFFICE_LOCATION is also an empty string.

Anthony.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:Mannaraprayil
ID: 13843621
Means..

WHERE
(ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')

will return all the records if @officeLocation = ''

But
WHERE
[OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN @officeLocation ELSE [OFFICE_LOCATION] END

will return only those records where OFFICE_LOCATION = '' if  @officeLocation = ''
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13843656
I think the equivalent CASE statement would require reversing the THEN and ELSE expressions...

WHERE
[OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN [OFFICE_LOCATION] ELSE @officeLocation END
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13843691
Is @officelocation defaulted to an empty string?  If so you could opt for defaulting it to NULL instead and using

WHERE [OFFICE_LOCATION] = Coalesce(@officelocation, [OFFICE_LOCATION])

all just different ways of accomplishing the same thing.  I would expect that the performance hit is minimal in all cases.
0
 
LVL 1

Author Comment

by:mbosch
ID: 13843692
I meant:

WHERE
[OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN [OFFICE_LOCATION]  ELSE @officeLocation END


I'm trying to say that if they pass an empty string, then return all office locations, otherwise return just the one's that match the parameter office location.
0
 
LVL 1

Accepted Solution

by:
Mannaraprayil earned 105 total points
ID: 13843708
I assume your requirement is as follows.

Return the records for the OFFICE_LOCATION provided. If no OFFICE_LOCATION is provided, which means @officeLocation = '', then return all the records.

If this is the actual requirement you dont need to use CASE statement. Your first Where clause is enough.

WHERE
(ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')


0
 
LVL 1

Author Comment

by:mbosch
ID: 13843802
So I'm assuming that the general consensus is that

WHERE
(ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')

is better performance-wise than

[OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN [OFFICE_LOCATION]  ELSE @officeLocation END

Correct?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13843809
An alternative is to use the LIKE function

WHERE ft.OFFICE_LOCATION LIKE '%' + @officelocation + '%'

then it doesn't matter what the user passes in and allows for nonspecific values.  Once again this depends on exactly what functionality you are looking for
0
 
LVL 1

Expert Comment

by:Mannaraprayil
ID: 13843838
Sorry BriCrowe,

WHERE ft.OFFICE_LOCATION LIKE '%' + @officelocation + '%'
will not return all office locations  if they pass an empty string.
0
 
LVL 1

Expert Comment

by:Mannaraprayil
ID: 13843877
But BriCrowe,
Well it will return return all office locations if and only if @officelocation is of type VARCHAR. This will not work with CHAR. So these things will depend on many things.... :-)

0
 
LVL 8

Assisted Solution

by:anthonywjones66
anthonywjones66 earned 90 total points
ID: 13843922
MBosch,

Lets just say it has a better chance.  We do know this, the query engine will resolve expressions that do not involve fields in a rowset.  So in the first case before processing any records the query engine will resolve this:-

(ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')

to

(ft.OFFICE_LOCATION = '' or true)  --assuming @officeLocation is ''

Now whether it takes the further step of eliminating the expression altogether I don't know again it probably has a better chance if it were this way round:-

(@officeLocation = '' or ft.OFFICE_LOCATION = @officeLocation)

Anthony.

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 90 total points
ID: 13844001
Agree with Anthony.  You should specify the check for @officeLocation = '' first:

WHERE (@officeLocation = '' OR ft.OFFICE_LOCATION = @officeLocation)

to take better advantage of a logical "short-circuit" (that is, the second condition never being checked because the first one is TRUE and is followed by OR).

Also agree with the earlier comment that this coding is easier to follow, which also makes it easier to change later.
0
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 90 total points
ID: 13846455
note, the two alternatives are not logically the same.

WHERE (ft.OFFICE_LOCATION = @officeLocation or @officeLocation = '')

WHERE [OFFICE_LOCATION] = CASE @officeLocation WHEN '' THEN [OFFICE_LOCATION]  ELSE @officeLocation END

in the first case, if @officeLocation = '', it returns all rows.(as suggested elsewhere it may be more efficient to swap the order, depending on your data)

in the second case, when @officeLocation = '' it still has to do a comparison between OFFICE_LOCATION and OFFICE_LOCATION before it decides to return the row. The difference arises when OFFICE_LOCATION is NULL. These rows will NOT be returned.

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13846559
BillAn1:

That's an *excellent* point ... NULL does *not* equal NULL (*unless* SET ANSI_NULLS OFF has been specified, in which case NULLs *are* considered equal (or unequal, if applicable)).
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question