May 20, 2010 at 1:16 am
Hi All
Following attachment is a sql plan of a query that i have to tune
query is
insert into TblTotCallAct select count(*)as 'Counts',r.custnumber from tblrecord r,tblimport i with (nolock) where r.Followupdate between dateadd(day,-7,i.Follow_up_date) and i.Follow_up_date and r.custnumber=i.custnumber
group by r.custnumber
Please suggest
Regards
May 20, 2010 at 2:18 am
Please post table definitions and index definitions, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2010 at 6:37 am
I got an error trying to read the plan. You might recheck the posting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2010 at 1:19 am
Maybe it's overkill, but I would probably write your query like this...
As far as indexing goes, my first inclination is to drop index [tblrecord].[idx_Followupdate] and replace it with 2 new indexes. The first is on r.custnumber with r.followupdate as an included column, and the second is on i.custnumber with i.follow_up_date as an included column.
Without the tables, it's hard to know for sure though. But, with those two indexes and the query below, you should be good to go.
WITH TEMP_1 AS
(
SELECT
r.custnumber
,r.followupdate AS rFUD
,i.follow_up_date AS iFUD
,DATEADD(dd,-7,i.follow_up_date) AS iFUD7
FROM
tblrecord r
INNER JOIN
tblimport i (NOLOCK) ON r.custnumber = i.custnumber
)
INSERT INTO
TblTotCallAct
SELECT
COUNT(1) AS 'Counts'
,t1.custnumber
FROM
TEMP_1 t1
WHERE
t1.rFUD BETWEEN t1.iFUD7 AND t1.iFUD
GROUP BY
t1.custnumber;
May 21, 2010 at 10:09 am
This will speed up further
WITH TEMP_1 AS
(
SELECT
r.custnumber
,r.followupdate AS rFUD
,i.follow_up_date AS iFUD
,DateDiff(dd,r.followupdate,i.follow_up_date) as NoOfDays
FROM
tblrecord r
INNER JOIN
tblimport i (NOLOCK) ON r.custnumber = i.custnumber
)
INSERT INTO
TblTotCallAct
SELECT
COUNT(1) AS 'Counts'
,t1.custnumber
FROM
TEMP_1 t1
WHERE
NoOfDays >0 and NoOfDays <=7
GROUP BY
t1.custnumber;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply