February 18, 2011 at 12:38 am
I have a query which is using a IntegerListType, havng implemented the integerlisttype it nows runs over 4 minutes to return a result set, which is unacceptable.
I like using the IntegerListType as passing int values from my C#.Net application is very easy and nice with ADO.Net.
Have a look at the 2 query's below, they are similar in content except for the 'IN' statement, 1 uses hard coded values, the other uses the intgerlisttype with the same values.
This query takes 7 seconds: No Probs with this...
select
@Sales = IsNull(sum(h.saleamt),0)
from
#possales h
Join BIM.dbo.possaleitem POI on(H.uid = POI.possaleheaderuid)
Join BIM.dbo.positem PIT on(POI.positemuid = PIT.uid)
Join BIM.dbo.poscategory PC on(PIT.poscategoryuid = PC.uid)
where
((h.transactiondate >= @tempDate) and (h.transactiondate < DATEADD(day,1,@tempDate)))
and
datepart(hh,h.transactiondate) = @hourid
and
h.posoutletuid IN (1,2,3,4,5)
Declaring an IntegerListType with the same values in it takes 4 minutes ++... why ?
DECLARE @OutletList IntegerListType
INSERT @OutletList(n) VALUES(1),(2),(3),(4),(5)
select
@Sales = IsNull(sum(h.saleamt),0)
from
#possales h
Join BIM.dbo.possaleitem POI on(H.uid = POI.possaleheaderuid)
Join BIM.dbo.positem PIT on(POI.positemuid = PIT.uid)
Join BIM.dbo.poscategory PC on(PIT.poscategoryuid = PC.uid)
where
((h.transactiondate >= @tempDate) and (h.transactiondate < DATEADD(day,1,@tempDate)))
and
datepart(hh,h.transactiondate) = @hourid
and
h.posoutletuid IN (SELECT n FROM @OutletList)
February 18, 2011 at 1:11 am
February 18, 2011 at 3:23 am
Does the performance improve if you add OPTION (RECOMPILE) to the statement?
http://msdn.microsoft.com/en-us/library/ms181714.aspx
It could be parameter sniffing of some kind.
February 18, 2011 at 2:29 pm
Apologies for not providing more information as to the execution plan...
I was able to resolve this issue by making use of temp tables..
The saleitem table which contains alot of data was slowing the query down, so I created 2 temp tables for the saleheader and saleitem table then I loaded these tables at the start of stored procedure and filtered the data down by the @StartDate and @EndDate parameters, which reduced the amount of data the query needed to work with.
For Example:
SELECT * INTO #possales FROM bim.dbo.possaleheader h WHERE h.transactiondate between @tempDate and @tempEndDate and h.posoutletuid IN (SELECT n FROM @OutletList)
SELECT i.* INTO #possaleitem
FROM bim.dbo.possaleitem i
JOIN bim.dbo.possaleheader h on(i.possaleheaderuid = h.uid)
WHERE
h.transactiondate between @tempDate and @tempEndDate and h.posoutletuid IN (SELECT n FROM @OutletList)
Then my query uses these temp tables instead of the actual tables, which increases the performance nicely....
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply