November 22, 2008 at 10:53 am
I am trying to optimize a stored procedure on SQL Server 2005 but I keep getting some weird results. I am comparing the following calls:
exec ConsumptionByPeriodAndCategoryList '2008-11-01 00:00:00', '2008-12-01 00:00:00', '46,47'--0.437 seconds
and
exec ConsumptionByPeriodAndCategoryList '2008-11-01 00:00:00', '2008-12-01 00:00:00', '46'--6.483 seconds!
The 3:rd parameter is a list of categories that should be taken into account, so one would expect the latter call to be faster than the former call. Instead, the opposite is true!
This is the SP:
ALTER PROCEDURE [dbo].[ConsumptionByPeriodAndCategoryList]
@fromdate datetime,
@todate datetime,
@IDs varchar(1000)
AS
BEGIN
DECLARE @SQL varchar(6000)
SET @SQL =
'SET NOCOUNT ON;
select d.deviceid,d.customerplace,d.channel, min(mv.value) as startvalue,max(mv.value) as endvalue, max(mv.value)-min(mv.value) AS total, u.name as unitname, u.specification,mt.name as modetypename from device d
inner join series s
on s.deviceid=d.deviceid
left outer join measurevalue mv
on mv.seriesid=s.seriesid
inner join measurestatus ms
on ms.measurestatusid=mv.measurestatusid
inner join mode m
on m.modeid=s.modeid
inner join unit u
on m.unitid=u.unitid
inner join modetype mt
on mt.modetypeid=m.modetypeid
inner join device_category dc
on dc.deviceid=d.deviceid
where dc.categoryid in ('+@IDs+')
and ms.name=''ok''
and mv.time between '''+convert(varchar,@fromdate)+''' and '''+convert(varchar,@todate)+'''
and mt.name <> ''instantaneous''
group by u.name,mt.name,s.seriesid, d.customerplace, d.deviceid, u.specification,d.channel
order by d.customerplace'
EXEC(@SQL)
END
I can see some differences in the actual execution plan, the faster call uses one extra "parallellism" and "hash match" while the slower call uses one extra "nested loop (inner join)". If this is unclear I could post more details.
All ideas are welcome.
///Jonas
December 15, 2008 at 2:04 pm
your query is fairly simple, why do you need to use dynamic sql?
Here is a good article to read.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
for your ID's parameter I suggest using the last method where you create a function that parses the IDS
SELECT d.deviceid,d.customerplace,d.channel, min(mv.value) as startvalue,max(mv.value) as endvalue, max(mv.value)-min(mv.value) AS total, u.name as unitname, u.specification,mt.name as modetypename
FROM device d
INNER JOIN series s
on s.deviceid=d.deviceid
LEFT OUTER JOIN measurevalue mv
on mv.seriesid=s.seriesid
INNER JOIN measurestatus ms
on ms.measurestatusid=mv.measurestatusid
INNER JOIN mode m
on m.modeid=s.modeid
INNER JOIN unit u
on m.unitid=u.unitid
INNER JOIN modetype mt
on mt.modetypeid=m.modetypeid
INNER JOIN device_category dc
on dc.deviceid=d.deviceid
WHERE dc.categoryid in (select OrderID from dbo.SplitOrderIDs(@IDs)
and ms.name='ok'
and mv.time between @fromdate) and @todate)
and mt.name <> 'instantaneous'
GROUP BY u.name,mt.name,s.seriesid, d.customerplace, d.deviceid, u.specification,d.channel
ORDER BY d.customerplace
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply