SQL Query Execution using parameter

Question

I am having problems figuring out why a query is taking drastically longer to run based on me swapping out a parameter against a real value.

DECLARE @quarter int
DECLARE @year int
DECLARE @countOfUnitsBought int

set @year = 2009
set @quarter = 1
set @countOfUnitsBought = 4;

with res
as
(
select
o.account_id
--,orderyear
--,orderquarter      
from
fmtables.[dbo].[orders] o     
--cross apply(values(year(o.[ship_date]))) as a1(orderyear)
--cross apply(values(DatePart(quarter,(o.[ship_date])))) as a2(orderquarter)    
where 
   ship_date = (select min(ship_date) from fmtables.[dbo].[orders] mo where [account_id] = o.account_id) and 
   total_value > 0 AND 
   order_status NOT LIKE 'return%' AND 
   order_status NOT LIKE 'cancel%' AND 
   order_status NOT LIKE 'freeze%' and   
   CAST(DatePart(quarter,(o.[ship_date])) as int) = @quarter and
   year(o.[ship_date]) = @year and
    (select sum(quantity) from fmtables..[orders] ox    inner join fmtables..[orderlines] olx on ox.order_id = olx.order_id  
                      where olx.order_id = o.order_id and [product_code] in(select [product_code] from fmtables..[products] where [category_code] in('1','2','3','4'))) >= @countOfUnitsBought

)
select * from res;

This query takes 43 seconds to run.

Now if I simply replace the @quarter and change to a literal

CAST(DatePart(quarter,(o.[ship_date])) as int) = 1 and

it now takes 1 second.

Can anyone please give me a clue as to why and if I need to change some casting to help. Thanks Scott

EDIT:

So I manage to get it whizzing through with the help of everyone's comments. I used a mix of passing the parameters from the inputs and then through to a 'local' variable inside the procedure.

alter procedure [dbo].[Lifetime_HeadsetUnits]
 @inquarter int ,  @inyear int,  @incountOfUnitsBought int
as
DECLARE @quarter int
DECLARE @year int
declare @countOfUnitsBought int

select @quarter = @inquarter
select @year = @inyear
select @countOfUnitsBought = @incountOfUnitsBought

and also the OPTION (OPTIMIZE FOR(@quarter = 1))
as part of the final output query.

A solution to the same problem is described here: stackoverflow.com/questions/11119432/… - You could probably start out by trying OPTION (OPTIMIZE FOR(@quarter = 1)) — Svein Fidjestøl
– Svein Fidjestøl, Commented Mar 3, 2015 at 13:53
Do you know how to look at query plans? If that is your real query and not just a demo for the sake of the question then it shouldn't be too hard to see the differences between the two alternatives. — Rhys Jones
– Rhys Jones, Commented Mar 3, 2015 at 13:55
The value for the @quarter will change as it going to be inside a loop. I will look at the option setting. Thanks — scottsanpedro
– scottsanpedro, Commented Mar 3, 2015 at 13:59
two words PARAMETER SNIFFING, read it:realsqlguy.com/dont-get-slimed-bad-parameter-sniffing — jean
– jean, Commented Mar 3, 2015 at 14:34

t-clausen.dk · Accepted Answer · 2015-03-03 14:32:40Z

1

Try this instead. I rewrote the datepart so indexes can be, used and the database is not making the long calculation on all rows. In other words I made your datecalculation sargable:

DECLARE @quarter int
DECLARE @year int
DECLARE @countOfUnitsBought int

set @year = 2009
set @quarter = 1
declare @from datetime = dateadd(quarter, @quarter - 1, cast(@year as char(4)))

set @countOfUnitsBought = 4;

with res
as
(
  select
  o.account_id
  from
    fmtables.[dbo].[orders] o     
  where 
     ship_date = 
      (select min(ship_date) 
       from fmtables.[dbo].[orders] mo
       where [account_id] = o.account_id) and 
   total_value > 0 AND 
   order_status NOT LIKE 'return%' AND 
   order_status NOT LIKE 'cancel%' AND 
   order_status NOT LIKE 'freeze%' and   
   o.[ship_date] >= @quarter and
   o.[ship_date] < DATEADD(QUARTER, 1, @from) and
    (select sum(quantity) from fmtables..[orders] ox    
    inner join fmtables..[orderlines] olx on ox.order_id = olx.order_id  
    where [product_code] in(select [product_code] from fmtables..[products] 
    where [category_code] in('1','2','3','4'))) >= @countOfUnitsBought
)
select * from res;

Are you running an sql-server 2008 ? There is a bug that could also explain your performance issues.

edited Mar 3, 2015 at 14:32

answered Mar 3, 2015 at 14:07

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scottsanpedro Over a year ago

I solved it without your answer though I am sure yours is just as valid.

scottsanpedro Over a year ago

I did comment, and said thank you. I truly do appreciate anyone who helps me on here. I just solved it before hand.

Collectives™ on Stack Overflow

SQL Query Execution using parameter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related