Fast in SMS Query but slow in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Fast in SMS Query but slow in SP

Author

Topic

peo
Starting Member

15 Posts

Posted - 2015-03-23 : 08:42:29

When I test this select in SMS Query Window it is fast, much less than a second.

select convert(Date,E.SMPTIME)as [Date],round(AVG(E.TT_OUTDOOR_AVG),1) as AvgTemp , SUM(E.ENERGY) as SumEnergy,SUM(E.AUX_ENERGY) as SumAux_Energy
, Sum(E2.ENERGY_3) as SumEnergy3 , Sum(E2.ENERGY_4) as SumEnergy4 , Sum(E2.ENERGY_5) as SumEnergy5 , Sum(E2.ENERGY_6) as SumEnergy6 ,
Sum(E2.ENERGY_7) as SumEnergy7 , Sum(E2.ENERGY_8) as SumEnergy8
from ENERGYSTATISTICS E left join ENERGYSTATISTICS2 E2
on E.PAID = E2.PAID and E.SMPTIME = E2.SMPTIME
Where
E.PAID='001981#2' And E.SMPTIME > '2015-01-01' and E.SMPTIME < '2015-03-23'
group by convert(Date,E.SMPTIME) order by convert(Date,E.SMPTIME)

But When I put it into a SP it takes minutes !!

ALTER PROCEDURE [dbo].[GetEStatPerDay]
@PAID nchar(20),
@DateStart date,
@DateEnd date
AS
BEGIN
SET NOCOUNT ON;
select convert(Date,E.SMPTIME)as [Date],round(AVG(E.TT_OUTDOOR_AVG),1) as AvgTemp , SUM(E.ENERGY) as SumEnergy,SUM(E.AUX_ENERGY) as SumAux_Energy
, Sum(E2.ENERGY_3) as SumEnergy3 , Sum(E2.ENERGY_4) as SumEnergy4 , Sum(E2.ENERGY_5) as SumEnergy5 , Sum(E2.ENERGY_6) as SumEnergy6 ,
Sum(E2.ENERGY_7) as SumEnergy7 , Sum(E2.ENERGY_8) as SumEnergy8
from ENERGYSTATISTICS E left join ENERGYSTATISTICS2 E2
on E.PAID = E2.PAID and E.SMPTIME = E2.SMPTIME
Where
E.PAID=@PAID And E.SMPTIME > @DateStart and E.SMPTIME < DATEADD (dd,1,@DateEnd)
group by convert(Date,E.SMPTIME) order by convert(Date,E.SMPTIME)
END

Why ?

Thnx in advance.

/P

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 08:55:30

Try adding WITH RECOMPILE

peo
Starting Member

15 Posts

Posted - 2015-03-23 : 09:34:40

OK, that cured the worst, but it still takes between 3 and 7 seconds with the SP compared to less than one from the query window.

/P

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 10:05:16

This may be a problem with parameter sniffing. The query plan that is cached may not be Optimal for the particular Parameters you are passing in, even though the query itself has not changed. For example, if you pass in a Parameter which only retrieves 10 out of 1,000,000 rows, then the Query Plan created may use a Hash Join, however if the Parameter you pass in will use 750,000 of the 1,000,000 rows, the Plan created may be an Index Scan or Table Scan.

So, it may work great for some parameters and lousy for others. One cure is to build custom plans for different parameter sets. Another is to ensure that your predicates in JOINS and WHERE clauses are SARGable and indexed.

peo
Starting Member

15 Posts

Posted - 2015-03-23 : 11:01:09

Well, in this case I have been using the same parameters all the time, just to be sure that the resulting query times is comparable. A difference between the query window and the SP is the hard coded dates in the query window. Can this cause the big difference in query times?

/P

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 11:04:25

quote:
Originally posted by peo

Well, in this case I have been using the same parameters all the time, just to be sure that the resulting query times is comparable. A difference between the query window and the SP is the hard coded dates in the query window. Can this cause the big difference in query times?

/P

Sure can! Are the date columns indexed? Also, the compiler may not have generated an optimal parameterized query. You should see what was cached.

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:55:06

quote:
Originally posted by peo

A difference between the query window and the SP is the hard coded dates in the query window. Can this cause the big difference in query times?

Usually not, but you have replaced a constant NOT with a @parameter, but with a function:


DATEADD (dd,1,@DateEnd)

that can cause SQL optimiser to table-scan instead of using a date.

Pre-calculate the cutoff date as a parameter.

Also please note:


 E.SMPTIME > '2015-01-01'

punctuation in dates make sthem ambiguous. Even though it works today it is dependant on Locale of the server and the user's settings such as LANGUAGE. Only use string dates in the format 'yyyymmdd' or ISO 'yyyy-mm-ddThh:mm:ss'

Do you mean


E.SMPTIME > @DateStart

rather than >= ??

(I would expect the @END to be one day MORE than the endpoint, and thus use a < test for that)


convert(Date,E.SMPTIME)

in the Select, Group By and Order By may be hurting too. I can;t think of a way around that, other that splitting the field into separate Date / Time columns. Might not be an issue though.

peo
Starting Member

15 Posts

Posted - 2015-04-22 : 07:59:52

Sorry for late responding. Had to deal with other stuff fore a while.

Yes, the date and PAID columns are indexed.
I changed to precalculated date range parameters and that speeded up the query to acceptable level.

Thanks for your help.

/P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources