Query fast from QA, slow from APIs

[DeltaMira]

hi Guys,
I have a query which runs for 20 sec in Query Analyzer but from API it takes more than 5 minutes. I would say it was in the application but I monitored it and I see it is neither the application code nor the connection time which slows it down - it just runs so slow when started from application!

The strange thing is that some week ago it was equally fast from api and from Query Analyzer.

Had anyone got problem like this? Or any ideas?

 

[onpnt]

How did you monitor it? Profiler? What technology is the application sitting on? What provider are you using? Have you tried different providers to see results in the performance. Also have you tested a different query to see if it truly is this particular query that is suffering?

I am Tedward Keyboardhands!!!
_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[DeltaMira]

Yes, profiler, but I also watched the time for the creation of each of the temp tables that I have inside the stored proc - if I run through QA it takes seconds to create each, started from the api-s it takes minutes!

Different query - yes, works OK. The applications that show the results of this particular query are VB and C# apps. The provider is OLEDB.

 

[vbkris]

>>that I have inside the stored proc - if I run through QA it takes seconds to create each, started from the api-s it takes minutes

did u use the same user for connecting in query analyser that the application uses?

>>but I also watched the time for the creation of each of the temp tables
how are you checking this???

Known is handfull, Unknown is worldfull

 

[gmmastros]

I would suggest that you try re-writing your procedure to use table variables instead of temp tables. Most of the time, table variables perform better than temp tables.

If you're unsure how to do this, then post some code snippets, and we'll show how to convert it.

-George

"the screen with the little boxes in the window." - Moron

 

[DeltaMira]

you think it might be the user, vbkris? i will give it a try with sa to see what happens....

I found sth more in the meantime - when I put the stored proc to execute in DTS, again it is terribly slow!???

I will let you know what happens with the same user

 

[DeltaMira]

George, I tryed this already but no success... I rewrited the proc to look like this:

CREATE procedure dbo.crs_monitoring7

@BU as varchar(3), -- ICE. ; FRF. ; FRP.
@DateFrom1 as varchar(10),
@DateTo1 as varchar(10),
@net as int, -- 0.Whole Country; 1.Own Network 2.Big Representatives
@route as varchar(5) -- Any existing route (A.;A1.;A11.;A111)

--test values
--set @DateFrom = '01/01/2007'
--set @DateTo = '06/08/2007'
--set @BU = 'ICE'
--set @net = 0
--set @route = '+0'

--WITH RECOMPILE
AS


Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON


declare @df as int,
@dt as int,
@rtname as varchar(30),
@DateFrom as varchar(10),
@DateTo as varchar(10)

set @DateFrom = @DateFrom1
set @DateTo = @DateTo1

set @df = 365
set @dt = 365

if @net = 0 goto WHOLE
goto ENDP

WHOLE:
set @rtname = 'WHOLE COUNTRY'

DECLARE @CYWC6 TABLE

(
Date datetime,
pcsCY numeric(14,0),
pcsCYa numeric(14,0),
valCY numeric(14,4),
valCYa numeric(14,4)
)


DECLARE @LYWC5 TABLE

(
Date datetime,
pcsLY numeric(14,0),
pcsLYa numeric(14,0),
valLY numeric(14,4),
valLYa numeric(14,4)
)

DECLARE @BY7 TABLE

(
Date datetime,
bpcsCY numeric(14,0),
bpcsCYa numeric(14,0),
bvalCY numeric(14,4),
bvalCYa numeric(14,4)
)

--makedummy SALESCY
insert into @CYWC6
select CalendarDate as Date, convert(numeric(14,0),isnull(sum(S.Pieces),0)) pcsCY,
convert(numeric(14,0),
(select isnull(sum(SS.Pieces),0)
from SALESY07 SS
where SS.Date>=convert(datetime, @DateFrom,103) and SS.Date<=CalendarDate and SS.ProdType= @BU)) as pcsCYa,
isnull(sum(S.Value),0) as valCY,
(select isnull(sum(SS.Value),0)
from SALESY07 SS
where SS.Date>=convert(datetime, @DateFrom,103) and SS.Date<=CalendarDate and SS.ProdType= @BU) as valCYa
--into #CYWC6
from GetCalendar2Y7 CY7
left outer join SALESY07 S on
CY7.CalendarDate = S.Date
where CalendarDate between convert(datetime,@DateFrom,103) and convert(datetime,@DateTo,103) and (S.ProdType= @BU or S.ProdType is null)
group by CalendarDate, S.Date

--make dummy SALESLY
insert into @LYWC5
select CalendarDate as Date, convert(decimal(14,0),sum(S.Pieces)) pcsLY,
convert(decimal(14,0),
(select sum(SS.Pieces)
from SALESY06 SS
where SS.Date>=convert(datetime, @DateFrom,103)-@df and SS.Date<=CalendarDate and SS.ProdType= @BU)) as pcsLYa,
sum(S.Value) as valLY,
(select sum(SS.Value)
from SALESY06 SS
where SS.Date>=convert(datetime, @DateFrom,103)-@df and SS.Date<=CalendarDate and SS.ProdType= @BU) as valLYa
--into #LYWC5
from GetCalendar2Y6 CY6
left outer join SALESY06 S on
CY6.CalendarDate = S.Date
where CalendarDate between convert(datetime,@DateFrom,103)-@df and convert(datetime,@DateTo,103)-@dt and (S.ProdType= @BU or S.ProdType is null)
group by CalendarDate, S.Date

--makedummy BUDGETCY
insert into @BY7
select CalendarDate as Date, convert(decimal(14,0),sum(B.Pieces)) bpcsCY,
convert(decimal(14,0),
(select sum(BB.Pieces)
from BUDGETY07 BB
where BB.Date>=convert(datetime, @DateFrom,103) and BB.Date<=CalendarDate and BB.ProdType= @BU)) as bpcsCYa,
-- sum(S.Boxes) bxsCY,
sum(B.Budget) as bvalCY,
(select sum(BB.Budget)
from BUDGETY07 BB
where BB.Date>=convert(datetime, @DateFrom,103) and BB.Date<=CalendarDate and BB.ProdType= @BU) as bvalCYa
--into #BY7
from GetCalendar2Y7 CY7
left outer join BUDGETY07 B on
CY7.CalendarDate = B.Date
where CalendarDate between convert(datetime,@DateFrom,103) and convert(datetime,@DateTo,103) and (B.ProdType= @BU or B.ProdType is null)
group by B.Date, CalendarDate

select case when datename(dw,CY.Date) is null then datename(dw,LY.Date+@df)
else datename(dw,CY.Date)
end as DayName,
case when CY.Date is null then LY.Date+@df
else CY.Date
end as Date
--CY by day
, isnull(pcsCY,0) as pcsCY
, convert(decimal(14,1),isnull(valCY,0)) as valCY
, convert(decimal(14,3),(case when pcsCY = 0 or pcsCY is null then 0
else valCY/pcsCY
end)) AvPrCY
--LY by day
, isnull(pcsLY,0) as pcsLY
, convert(decimal(14,1),isnull(valLY,0)) as valLY
, convert(decimal(14,3),(case when pcsLY = 0 or pcsLY is null then 0
else valLY/pcsLY
end )) as AvPrLY
--CY/LY %
, case when pcsLY <> 0 and pcsLY is not null and pcsCY is not null and pcsCY<>0
then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(pcsCY-pcsLY))/pcsLY)) + '%'
when pcsLY is null then '0.00%'
when pcsLY <> 0 and pcsLY is not null and (pcsCY is null or pcsCY = 0) then '-100.0%'
else '0.00%' end as pcsCY_LYps
, case when valLY <> 0 and valLY is not null and valCY is not null and valCY<>0
then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(valCY-valLY))/valLY)) + '%'
when valLY is null then '0.00%'
when valLY <> 0 and valLY is not null and (valCY is null or valCY = 0) then '-100.0%'
else '0.00%' end as valCY_LYps
--CY/LY diff
, case when pcsCY is not null then convert(decimal(14,0),isnull(pcsCY-pcsLY,0))
else convert(decimal(14,0),isnull(-1*pcsLY,0))
end as pcsCYDiff
, case when valCY is not null then convert(decimal(14,1),isnull(valCY-valLY,0))
else convert(decimal(14,1),isnull(-1*valLY,0))
end as valCYDiff
--CY accumulative
, isnull(pcsCYa,0) as pcsCYa
, convert(decimal(14,2),isnull(valCYa,0)) as valCYa
, convert(decimal(14,2), (case when pcsCYa = 0 or pcsCYa is null then 0
else valCYa/pcsCYa
end)) as AvPrCYa
--LY accumulative
, isnull(pcsLYa,0) as pcsLYa
, convert(decimal(14,2),isnull(valLYa,0)) as valLYa
, convert(decimal(14,2), (case when pcsLYa = 0 or pcsLYa is null then 0
else valLYa/pcsLYa
end)) as AvPrLYa
--CYa/LYa %
, case when pcsLYa <> 0 and pcsLYa is not null and pcsCYa is not null and pcsCYa <>0
then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(pcsCYa-pcsLYa))/pcsLYa)) + '%'
when pcsLYa is null then '0.00%'
when pcsLYa <> 0 and pcsLYa is not null and (pcsCYa is null or pcsCYa = 0) then '-100.0%'
else '0.00%' end as pcsCYa_LYaps
, case when valLYa <> 0 and valLYa is not null and valCYa is not null and valCYa <>0
then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(valCYa-valLYa))/valLYa)) + '%'
when valLYa is null then '0.00%'
when (valLYa <> 0 and valLYa is not null) and (valCYa is null or valCYa = 0) then '-100.0%'
else '0.00%' end as valCYa_LYaps
--CYa/LYa diff
, case when pcsCYa is not null then convert(decimal(14,0),isnull(pcsCYa-pcsLYa,0))
else convert(decimal(14,0),isnull(-1*pcsLYa,0))
end as pcsCYaDiff
, case when valCYa is not null then convert(decimal(14,1),isnull(valCYa-valLYa,0))
else convert(decimal(14,1),isnull(-1*valLYa,0))
end as valCYaDiff
--Budget CYa
, isnull(bpcsCYa,0) as bpcsCYa
, convert(decimal(14,1),isnull(bvalCYa,0)) as bvalCYa
--CY/Budget %
, case when bpcsCYa <> 0 then convert(varchar,convert(decimal(14,1),convert(decimal(14,0),100*(pcsCYa-bpcsCYa))/bpcsCYa)) + '%' else '0.00%' end as pcsBdgPs
, case when bvalCYa <> 0 then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(valCYa-bvalCYa))/bvalCYa)) + '%' else '0.00%' end as valBdgPs
, case when bvalCYa <> 0 then convert(varchar,convert(decimal(14,1),convert(decimal(14,2),100*(valCYa-bvalCYa))/bvalCYa)) + '%' else '0.00%' end as valBdgPs
--Budget/CY diff
, convert(decimal(14,0),isnull(pcsCYa-bpcsCYa,0)) as bpcsCYDiff
, convert(decimal(14,1),isnull(valCYa-bvalCYa,0)) as bvalCYDiff
, @DateFrom DateFrom
, @DateTo DateTo
, @rtname RtName
, case when CY.Date is null then DATENAME(MONTH,(LY.Date+@df))
else DATENAME(MONTH,(CY.Date))
end as Mo

from @CYWC6 CY
full outer join @LYWC5 as LY on
CY.Date-365 = LY.Date
left outer join @BY7 as BCY on
CY.Date = BCY.Date
order by month(CY.Date) desc, CY.Date asc

--drop table #LYWC5
--drop table #CYWC6
--drop table #BY7

goto ENDP

ENDP:

 

[gmmastros]

No success? I copied this to query analyzer and it parses just fine. Obviously, I don't have your tables, so I can't run it. What is it about this query that doesn't work?

-George

"the screen with the little boxes in the window." - Moron

 

[DeltaMira]

George, the problem about this query is that it runs fine )) but only from QA. I would prefer it performing equally slow from both places but now I am in big confusion - when I run it from QA it executes in seconds returning the wanted resultset, but when it is put for example even in a DTS it begins to do its crazy things and executes for nearly six minutes!

i am getting desperate with it.... nowhere in the forums could I find a similar problem...

 

[gmmastros]

Try figuring out where the performance problems are.

Temporarily modify the procedure to put in some timing code, like this...

Create procedure....
as

Declare @StartTime DateTime
Set @StartTime = GetDate()

[blue]-- Put a block of code here[/blue]

Select 'Time Check 1', DateDiff(Milliseconds, @StartTime, GetDate())
Set @StartTime = GetDate()

[blue]-- Put another block of code here[/blue]
Select 'Time Check 2', DateDiff(Milliseconds, @StartTime, GetDate())

By doing this... you will be able to see which part of this procedure is slow, and will therefore help in optimizing it.

-George

"the screen with the little boxes in the window." - Moron

 

[DeltaMira]

guys! it is something with the parameters! I put them as fixed values and it ran fast from the API!

...but of course this does not mean I have fixed it...

 

[DeltaMira]

and despite, i will give it a try as you suggest, George. I willonly write the results in a table to be able to see it after I run the procedure from the DTS

 

[gmmastros]

Do a google search on 'Parameter Sniffing'

Start here:

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

-George

"the screen with the little boxes in the window." - Moron

 

[onpnt]

Don't declare memory (another variable) for something that is already in a variable. Like the DateFrom and DateTo below.

declare @df as int,
@dt as int,
@rtname as varchar(30),
@DateFrom as varchar(10),
@DateTo as varchar(10)

set @DateFrom = @DateFrom1
set @DateTo = @DateTo1

Unless these values are going to be dynamic why are you using a variable to store a number like 365?

set @df = 365
set @dt = 365

Make the parameters datetime values also if that is indeed what is being sent. Use a tinyint of that int if all you need is 0, 1 or 2

CREATE procedure dbo.crs_monitoring7

    @BU as varchar(3),    -- ICE. ; FRF. ; FRP.
    @DateFrom1 as DateTime,
    @DateTo1 as DateTime,
    @net as tinyint,             -- 0.Whole Country; 1.Own Network 2.Big Representatives
    @route as varchar(5)         -- Any existing route (A.;A1.;A11.;A111)

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}