How may i speed up this query. It works as it is but is slow.
--previous 3 months data DECLARE @dStart datetime , @dEnd datetime set @dEnd = dateadd(month, datediff(month, -1, getdate()) - 1, -1) set @dStart = DATEADD(month, datediff(month, 0, getdate())-3, 0) With Call_CTE ([ID],[Customer],[month],[Device],[CallNo],[Date],[Email],[Phone],[Recived],[Priority],[CallType],[CallCategory],[Desc],[RespInSLA],[Fixed in SLA],[FixedFirstTime],[Row],[Lastjournal],[journaltype]) as ( select c2.CustID as [ID] ,c2.Company as [Customer] ,datename(month,(c1.closeddate)) as [month] ,c2.product as [Device] ,c2.CallID as [CallNo] ,c1.closeddate as [Date] ,c1.TrackerEMail as [Email] ,c2.PHONE1 as [Phone] ,c1.recvdDate as [Recived] ,(Case when c1.CallType='Monitoring Alert' then 'Alert' else c1.Priority end) as [Priority] ,c1.CallType as [CallType] ,c1.Category as [CallCategory] ,c1.CallDesc as [Desc] ,(Case when [SLAResponseMins]<=[RespMins] then 1 else 0 end) as [RespInSLA] ,(Case when [SLAFixMins] <= [FixMins] then 1 else 0 end) as [Fixed in SLA] ,(Case when j.JournalType = 'Change - Success' then 1 else 0 end) as [FixedFirstTime] ,ROW_NUMBER() over (PARTITION by c1.CallID order by c1.callid) as [Row] ,(Case when (select top 1 j.JournalType from dbo.Journal j inner join dbo.CallLog c2 on j.CallID = c2.CallID where c1.CallID = c2.CallID order by j.CallID, j.EntryDate + ' ' + j.EntryTime desc) in ('Internal','Third Party') then 'Internal log' else (select top 1 j.EntryText from dbo.Journal j inner join dbo.CallLog c2 on j.CallID = c2.CallID where c1.CallID = c2.CallID order by j.CallID, j.EntryDate + ' ' + j.EntryTime desc) end) as [Lastjournal] ,(select top 1 j.JournalType from dbo.Journal j inner join dbo.CallLog c2 on j.CallID = c2.CallID where c1.CallID = c2.CallID order by j.CallID, j.EntryDate + ' ' + j.EntryTime desc) as [journaltype] from dbo.CallLog c1 inner join dbo.Journal j on c1.CallID = j.CallID inner join dbo.subset c2 on c2.CallID = c1.CallID where (c1.closedDate) between @dStart and @dEnd and not(c2.CustID) is null and not(c1.recvdDate) is null ) select * from Call_CTE where row = 1;
From where I'm sitting this is all guesswork...... Is CallId the primary key for CallLog? If so the line ROW_NUMBER() over (PARTITION by c1.CallID order by c1.callid) as [Row] is going to return '1' for each and every row, meaning the final select select * from Call_CTE where row = 1; will return all rows. Running an aggregate windowed function for no point, is a waste. The subqueries that order by j.CallID, j.EntryDate + ' ' + j.EntryTime are going to consume a lot of processing, and it makes me think that j.EntryDate and j.EntryTime are not the right datatypes.
Row number is defined as the query brings up duplicates and i cant use distinct as there are text fields in there that are not comparable. I had to join the date time to get the last journal. I will try changing the datatypes to date time using cast as see if that helps. As far as the execution time is concerned it doe not look to bad. Client Execution Time 10:25:08 10:24:53 Query Profile Statistics Number of INSERT, DELETE and UPDATE statements 0 0 0.0000 Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0.0000 Number of SELECT statements 2 0 1.0000 Rows returned by SELECT statements 11 0 5.5000 Number of transactions 0 0 0.0000 Network Statistics Number of server roundtrips 9 3 6.0000 TDS packets sent from client 10 4 7.0000 TDS packets received from server 59 3 31.0000 Bytes sent from client 5044 4490 4767.0000 Bytes received from server 184675 518 92596.5000 Time Statistics Client processing time 1 1 1.0000 Total execution time 175 20 97.5000 Wait time on server replies 174 19 96.5000