January 23, 2008 at 8:51 am
I am running an application having sql server 2000 Enterprise Edition as a backend .
I am using a stored procedure in which cursor is used .
Since the performance is slow i have applied a trace in which i found
few statements which are taking more time.(above 500)
these are
1)declare @P1 float set @P1=5.298220000000000e+005 exec GETNKEY 'IKey', @P1 output select @P1
2)fetch next from cur into @n; (this statement is inside the procedure)
also i found textdata in trace like
-- Procedurename fetch next from cur into @n;
I did'nt understand "--" preceding the procedure name?
can anybody tell me why this much delay?
January 23, 2008 at 9:11 am
You're not giving us enough info to help you. The rest of the code is needed.
I'm assuming you know you should by far and large be avoiding cursors, since they tend to be very inefficient versus set-based methods (what SQL server prefers).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 11:24 pm
Following is the procedure i am calling from my code.
Prior to make call I am using declare statement
declare @P1 float set @P1=2.332100000000000e+005
exec GETNKEY 'AAGC', @P1 output select @P1
--The above two statements are shown in trace taking lot of time(576)
--Also the fetch next statement taking much time
CREATE PROCEDURE dbo.GETNKEY @kname nvarchar(255), @n integer OUTPUT
as
begin
--begin tran
declare cur cursor for
select keyvalue from LKEY with ( ROWLOCK, UPDLOCK) where keyname=@kname --for update
open cur;
begin
fetch next from cur into @n;
if @@FETCH_STATUS <> 0
begin
insert into lkey values(@kname,1);
set @n = 1;
end
else
begin
set @n = @n+1;
update dbo.LKEY set keyvalue=@n where current of cur;
end
end
--commit
close cur;
DEALLOCATE cur;
end
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply