troubleshooting Question

MS SQL 2008 Query with scalable variables and without Run Time Difference

Avatar of j_heck
j_heck asked on
Microsoft SQL Server 2008
6 Comments1 Solution584 ViewsLast Modified:
I have a query that runs in about 9 seconds when the actual date range is in the query.  When I create a DECLARE statement and assign the variables to a begin and end date the query ran for over 4 minutes before I ended it.  What would cause the big difference in run times?  

Here is the query with the declared variables being used and the actual dates commented out in the query.  This is puzzling because I want to have a second query run after this with variables that are generated by the first two via a select statement.  The user wants to be able to put in one set of dates and then generate data for first date range and select another set of dates for previous year same time frame.  

declare
      @BeginDate1 date,
      @EndDate1 date,

set @BeginDate1 = '2012-05-20'
set @EndDate1 = '2012-05-23'

Select t.StoreId
      ,Corporate = CASE WHEN si.Corporate = 1 THEN 'Corporate' ELSE 'Franchise' END
      ,sum(t.SoldPrice) AS sales
      ,SUM(t.Qty * t.UnitCost) as COGS  
      ,((SUM(soldprice) - SUM(t.Qty * t.UnitCost))/sum(soldprice))*100 As Margin
      ,COUNT(DISTINCT t.TransNbr) AS TransactionCount
Into #PreviousYear1    
From TLogDtl t with (nolock)
      LEFT OUTER JOIN StoreInfo si with (nolock) ON si.StoreId3 = t.StoreId
      LEFT OUTER JOIN Product p with (nolock) ON p.upc = t.upc
where t.TransDate between @BeginDate1 and @EndDate1   --'2012-05-20' and '2012-05-23'  --
      and si.corporate = 1
Group by t.StoreId, si.corporate
Order by t.StoreId, si.corporate


Thanks in advance for the help.
ASKER CERTIFIED SOLUTION
Jason Schlueter
IT Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
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 6 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