replacing cursors with sql query

i have written a cursor to select records in a sequence for processing but i think the performance is not upto the mark.i have read in many places that sql cursor should not be used.but i am unable to find a substitute for cursor.could you help me out with this..

this is the cursor we are using:

CREATE proc sp_process_cdr
as
declare @.last_call_time datetime
set @.last_call_time=(select max(calltime_gmt) from processed_cdr)
declare process_cdr cursor
read_only
for
select srcip,username,callto,calltime,duration from rawcdr
where calltime>@.last_call_time
declare
@.gatewayip varchar(50),
@.username varchar (50),
@.callto varchar(100),
@.calltime datetime,
@.duration float,
@.accountid varchar(50),
@.subscriberid varchar(10),
@.cost money,
@.country varchar(100)

open process_cdr
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
while(@.@.fetch_status<>-1)
begin
if(@.@.fetch_status<>-2)
begin
set @.accountid=''
set @.subscriberid=''
set @.country=''
set @.cost=0

if(charindex('00',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+3,50)
end
else
if(charindex('011',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+4,50)
end
else
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+1,50)

exec sp_process_call @.gatewayip,@.username,@.duration,@.callto,@.accountid output,@.subscriberid output,@.country output,@.cost output
insert into processed_cdr
select @.accountid,@.subscriberid,@.gatewayip,@.username,@.callto,@.country,@.calltime,@.duration,@.cost
end
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
end
close process_cdr
deallocate process_cdr

GO

i dont have a unique column in my table.

YOu are executing a stored procedure within your code, unless that you can′t change your stored procedure, you can′t keep away from that cursor (not touching the solution that you could put out some statement and execute them froma temporary table one by one, which would be nearly the same performance). Anyway you shouldn′t name your procedure with a "sp_" prefix unless these is predefined for master database procs and this will lead to performance (due to recompiling) issues.

HTH, Jens Suessmeyer.

|||

Dear Vignesh, below is an example of how to do update using query rather than cursor.

UPDATE t1
SET t1.column4 = t2.column4, t1.column5 = t3.column5
FROM table1 t1
INNER JOIN table2 t2 ON t1.column1 = t2.column1
INNER JOIN table3 t3 on t1.column2 = t3.column2
WHERE t1.column3 >= t2.column3 AND t3.column3 <> 0

Vincent