troubleshooting Question

SQL Query Spped

Avatar of mattkovo
mattkovo asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
15 Comments1 Solution334 ViewsLast Modified:
I have a simple query that runs slow when using a modified Where clause. I'm running MSSQL 2008R2.

If I use the following, the query speed is as expected:
where cir.storeid = @StoreID

However, if I change it up then it runs extremely slow. The difference is from less than a second to a few minutes.
where (@StoreID = -1 or (@StoreID != -1 and cir.storeid = @StoreID))

Why does it run slow like this and what can I do to improve the performance?

I attached the complete query.  Thanks for any help!

Declare @StoreID int = 74
	   ,@StartDate datetime = '6/1/2012'
	   ,@EndDate datetime = '6/30/2013'

Select cir.Guest_Check_Name as 'Item'
	   ,SUM(extension) as 'Sales'
	   ,SUM(qty) as 'Units'
        
from check_item_record cir
	 
where (@StoreID = -1 or (@StoreID != -1 and cir.storeid = @StoreID))
             and CHECK_FILE_DATE between @StartDate and @EndDate
 
group by cir.guest_check_name
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros