SQL Server Performance Forum – Threads Archive
Cursor Optimization
I m comparing 2 tables having records near 30Lakh.
these comparison based on the Cli,Destinationnumber,startdate and the duration recorded
by both the companies.
I have the clustured index on cli_mod,destinationnumber_mod,startdate on both tables This cursor taking 3 to 4 days for this comparison.
Plz tell me how i wil optimize this cursor so it wil take shorter time to compelete DECLARE @count int
DECLARE @Flag_mod INT DECLARE @Company2_serial int
DECLARE @company1_serial int DECLARE @company1_cli varchar(21) DECLARE @company1_destinationnumber varchar(24) DECLARE @company1_starttime datetime DECLARE @company2_duration float
DECLARE @company1_duration int Declare CUR1 CURSOR FOR
SELECT cli_mod,destinationnumber_mod,startdate,duration,Flag_mod,serial
FROM Company1
WHERE Flag_mod is null
order by cli_mod,destinationnumber_mod,startdate OPEN CUR1
FETCH NEXT FROM CUR1 INTO @company1_cli,@company1_destinationnumber,@company1_starttime,@company1_duration,@Flag_mod,@company1_serial
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SET @Count=0 WHILE @Count<=900 ———time tol 15 Minutes
BEGIN
IF EXISTS (
SELECT a_party_mod,b_party_mod,call_time,Duration FROM Company2
WHERE
a_party_mod = @company1_cli and
b_party_mod = @company1_destinationnumber and
abs(DATEdiff(SS,call_time,@company1_starttime))[email protected] and
abs(convert(int,Duration)[email protected]_duration) between 0 and 60 and
Flag_mod is null
) BEGIN
update Company2
set Flag_mod= @Count,@Company2_serial=serial
where
a_party_mod = @company1_cli and
b_party_mod = @company1_destinationnumber and
abs(DATEdiff(SS,call_time,@company1_starttime))[email protected] and
abs(convert(int,Duration)[email protected]_duration) between 0 and 60 and
Flag_mod is null
UPDATE Company1
SET [email protected]
WHERE
[email protected]_cli and
[email protected]_destinationnumber and
[email protected]_starttime and
[email protected]_duration and
Flag_mod is null
insert into matchrecords_mod values(@company1_serial,@Company2_serial,@Count,’S’)
BREAK
END SET @[email protected]+1
END
FETCH NEXT FROM CUR1 INTO @company1_cli,@company1_destinationnumber,@company1_starttime,@company1_duration,@Flag_mod,@company1_serial
END
CLOSE CUR1
DEALLOCATE CUR1
General Approach Update T
set T.col1=S.col1, T.col2=S.col2
from TargetTable T inner join SourceTable S
on T.keycol=S.keycol Madhivanan Failing to plan is Planning to fail
This cursor is doing record by record matching by matching columns
please see the cursor and tell me the ptimization
You should follow Madhivanan’s advise…
And your SELECT and UPDATE queries will not use indexes even indexes exists because you are using ABS and DATEDIFF fuctions…Change your WHERE clause to SARG so that it will use index which improve execution time… Beware of Search Argument (SARG) Data Types
http://www.sqlservercentral.com/col…fsearchargumentsargdatatypes_printversion.asp
http://www.sql-server-performance.com/jc_large_data_operations.asp
Ex:
update Company2
set Flag_mod= @Count,@Company2_serial=serial
where
a_party_mod = @company1_cli and
b_party_mod = @company1_destinationnumber and
call_time = dateadd (ss, [email protected], @company1_starttime)
–abs(DATEdiff(SS,call_time,@company1_starttime))[email protected] and
duration between [email protected]_duration and [email protected]_duration
–abs(convert(int,Duration)[email protected]_duration) between 0 and 60 and
Flag_mod is null Mohammed U.
The solution I have given also does the record by record matching by matching columns
Only thing is that you need to modify that code according to your logic Madhivanan Failing to plan is Planning to fail
I think you can write code without cursor by using some temp tables…
Mohammed U.
Can u give some example to write that code from the temp table
I need spend good amount of time to do it… when I get the time I will try <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Mohammed U.
Check this code without coursor…. make sure logic is correct… create table #temp ( cnt int identity(0,1), cli_mod varchar(21) ,destinationnumber_mod varchar(24),
startdate datetime,duration int,Flag_mod int,serial int) insert into #temp
SELECT cli_mod,destinationnumber_mod,startdate,duration,Flag_mod,serial
FROM Company1
WHERE Flag_mod is null
order by cli_mod,destinationnumber_mod,startdate BEGIN
update c
set Flag_mod= t.cnt
from Company2 c
join #temp t on c.a_party_mod = t.cli_mod and
b_party_mod = t.destinationnumber_mod
where
abs(DATEdiff(SS,c.call_time,t.startdate))=t.cnt and
abs(convert(int,c.Duration)-t.duration) between 0 and 60 and
c.Flag_mod is null
UPDATE c
SET c.Flag_mod=t.cnt
FROM Company1 C
join #temp t on c.cli_mod=t.cli_mod and
c.Destinationnumber_mod=t.destinationnumber_mod and
c.startdate=t.startdate and
c.Duration=t.Duration and
Flag_mod is null
insert into matchrecords_mod — values(@company1_serial,@Company2_serial,@Count,’S’)
select t.serial, c.serial, t.cnt, ‘S’
from Company2 c
join #temp t on c.a_party_mod = t.cli_mod and
b_party_mod = t.destinationnumber_mod
where
abs(DATEdiff(SS,c.call_time,t.startdate))=t.cnt and
abs(convert(int,c.Duration)-t.duration) between 0 and 60 and
c.Flag_mod is null
END
Mohammed U.
]]>