December 11, 2007 at 10:01 am
Hi Matjaz
The query executes at a similar execution time: 15:55
December 11, 2007 at 10:13 am
Hi PW
Just looking at pintable and although it does sound like the symptons we are experiencing are correct we do not pin any of our tables.
Thanks for your help so far.
December 11, 2007 at 10:20 am
Going back to your original post:
>>The maintenance plan remains the same regardless of the execution time. The maintenance plan doesnt use table scans only index seeks.
Just to confirm, you mean "execution plan", right ?
And you have verified that the plans are 100% identical ?
And you're seeing all objects accessed with "index seeks", right ? Not index scans or clustered index scans ?
Now that we've seen the SQL inside the function, it's clear that many more tables are involved. And with that many tables, it would be very rare to find every object accessed via an "index seek".
December 12, 2007 at 2:31 am
Just to confirm, you mean "execution plan", right ? Yes I did.
And you have verified that the plans are 100% identical ? Yes, I am looking more into the function now, there are index scans in the function but the cost on the scans are 0% and most of the tables with the Clustered Index Scans have either less than 20 rows or around 1500 rows.
There are Table Spool/Lazy Spool I'm just looking at that but cant seem to find way to avoid them. These apparantly use tempdb so I'm wondering if thats where the problem is?
December 12, 2007 at 4:50 am
More observations....
If I change the query to a select * instead of doing the calculations the query returns within 1/2 minute.
If I specify a column in the select then the execution time returns to over 10 minutes again.
December 12, 2007 at 8:57 am
This problem does seem to point an execution plan problem.
After searching through the forum i have changed the date hardcoded values to variables and the query now completes in 4 seconds, the query now looks like the following:
declare @date1 datetime
declare @date2 datetime
set @date1 = '2007-08-02 00:00:00'
set @date2 = '2007-11-01 23:59:59'
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost],
Avg(cast(intDuration as decimal)) as [AvgDuration] FROM
dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN
L3_Member_Instance ON dbo.CallSearchL3_2.intMemberID = L3_Member_Instance.intMemberID INNER JOIN
[Member Historic Data] ON [Member Historic Data].intMemberID = L3_Member_Instance.intMemberID
WHERE dbo.CallSearchL3_2.intBillID in (181,251,314,390,419,469,533,591,637,684,734,783,836) AND
[Member Historic Data].intBillID=836 AND dtmDateTime>=@date1 AND dtmDateTime<=@date2 AND chrTransmissionTypeID='V'
AND intRoamStatusID=1 AND bitWeekDay=1 AND bitOutOfHours=0 AND bitInternalCall=1 AND L3_Member_Instance.intGroupID
IN(1815,1720,1409,1410,1411,1722,1412,1468,1469,1470,1471,1472,1473,1163,1398,1422,1721,1423,1424,1425,1426,1427,1428,
1429,1430,1432,1433,1434,1435,1436,1759,1172,1438,1439,1440,1441,1442,1443,1444,1445,1448,1446,1447,1449,1450,1451,1452,1453,
1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1431,1466) AND L3_Member_Instance.intStructureID =29
The execution plan is different but I cant spot anything too obvious apart from a hash join i have added a loop join hint to the original query and it now takes less than 20 seconds to complete. I will pass the findings on to the development department and see what they want to do.
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost],
Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN
L3_Member_Instance ON dbo.CallSearchL3_2.intMemberID = L3_Member_Instance.intMemberID INNER loop JOIN
[Member Historic Data] ON [Member Historic Data].intMemberID = L3_Member_Instance.intMemberID
WHERE dbo.CallSearchL3_2.intBillID in (181,251,314,390,419,469,533,591,637,684,734,783,836) AND
[Member Historic Data].intBillID=836 AND dtmDateTime>='2007-08-02 00:00:00' AND dtmDateTime<='2007-11-01 23:59:59' AND chrTransmissionTypeID='V'
AND intRoamStatusID=1 AND bitWeekDay=1 AND bitOutOfHours=0 AND bitInternalCall=1 AND L3_Member_Instance.intGroupID
IN(1815,1720,1409,1410,1411,1722,1412,1468,1469,1470,1471,1472,1473,1163,1398,1422,1721,1423,1424,1425,1426,1427,1428,
1429,1430,1432,1433,1434,1435,1436,1759,1172,1438,1439,1440,1441,1442,1443,1444,1445,1448,1446,1447,1449,1450,1451,1452,1453,
1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1431,1466) AND L3_Member_Instance.intStructureID =29
Thanks for everyone helps.
December 12, 2007 at 3:07 pm
Maybe a mute point now but I would suggest using BETWEEN instead of >= and =@date1 AND dtmDateTime<=@date2" becomes "dtmDateTime BETWEEN @date1 AND @date2".
I would also evaluate your WHERE clause to ensure your most restrictive clauses, ie those that rule out the most records, are first and "=" before IN. If you can, move parts of your WHERE clause to the JOIN's ON as this will remove the records before they are joined rather than joining them and then removing them, eg L3_Member_Instance.intStructureID =29 and Member Historic Data].intBillID=836 can be moved to ON clauses to minimise the data to be evaluated by the WHERE statement.
April 4, 2008 at 11:57 am
Interesting post. I wonder why the variable made such as improvement to the query.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply