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

Replacing a cursor

I am currently evaluating the performance of an application which relies heavily on cursors. Below is an code example. I am looking for some good performance gains, is this the best method of updating rows of data?

DECLARE @.PAlloc_TimeID as int
DECLARE @.PAlloc_UniqueID as int
DECLARE PAlloc CURSOR FOR
SELECT TimeID,
UniqueID
FROM #PMatch
GROUP BY TimeID, UniqueID
HAVING SUM(Active) > 1

OPEN PAlloc
FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN

UPDATE P
SET Active = 0
FROM #PMatch P
WHERE P.TimeID = @.PAlloc_TimeID
AND P.UniqueID = @.PAlloc_UniqueID
AND P.ElementID > (
SELECT MIN(ElementID)
FROM #PMatch
WHERE TimeID = @.PAlloc_TimeID
AND UniqueID = @.PAlloc_UniqueID)

FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
END
CLOSE PAlloc
DEALLOCATE PAlloc

Normally, using a CURSOR is NOT the best way to update data. (However, in a few situations, it may be necessary.)

A set based operation (UPDATE) is usually the 'best' way to update. Consider if your process above could be re-written as a single UPDATE statement, perhaps using one or more sub-queries. (It might be easier than you think.)

|||

Yes... Arnie is right, It can be replace with single Update

Statement. But if the Database is gigantic then Updating in a single statement

is not preferred.

|||

u'll be able to write this query in one (or more) update statement(s)...or use a case in update statement.... most of the times u wont need a sursor at all.... and i guess even for a large database in this is a better solution..., as cursor on a large table will block resources for long...

sql