Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CURSOR - running very slow


CURSOR - running very slow

Author
Message
npyata
npyata
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
this is completed
Andrei Hetel
Andrei Hetel
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 182
You need to post the code, otherwise nobody can help you. We might find a way to avoid using a cursor.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
npyata (5/2/2013)
I am new to sql server 2008 R2. I have an issue with CURSOR. This is an simple cursor to get rows sequential and update an colum with flag based on some conditions which I am checking after the row is reterviewd. Can anyone suggest which is best way of using CURSOR to complete this process very fast. Any suggestions will be appreicated.

Narender Pyata


Yes the best way to use a cursor in this case to be fast is remove it. From your description there is absolutely no need for a cursor. I suspect that this cursor can be changed into a single update statement.

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
npyata
npyata
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
Here is the code. This is my first step in the process and after this seq_no update I need to update some other columns based on this seq_no.
USE [TESTDB]
GO
/****** Object: StoredProcedure [dbo].[sp_upd_match_eventlog] Script Date: 05/02/2013 11:17:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Author,Narender Pyata
-- Create date: 04/30/2013>
-- Description: Update match_event_log table log_seq_no
-- =============================================
ALTER PROCEDURE [dbo].[sp_upd_match_eventlog]
-- Add the parameters for the stored procedure here


AS
declare
@vr_seqno int,
@log_id int

declare log_data cursor for
select log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
order by log_id;

open log_data
fetch next from log_data
into @log_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Update statements for procedure here

BEGIN TRANSACTION Proc_IN

set @vr_seqno = @vr_seqno + 1;

Update [CAMINO].[dbo].[match_event_log]
set log_seqno = @vr_seqno
where log_id = @log_id;

COMMIT TRANSACTION Proc_IN;

END;
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
npyata (5/2/2013)
Here is the code. This is my first step in the process and after this seq_no update I need to update some other columns based on this seq_no.
USE [TESTDB]
GO
/****** Object: StoredProcedure [dbo].[sp_upd_match_eventlog] Script Date: 05/02/2013 11:17:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Author,Narender Pyata
-- Create date: 04/30/2013>
-- Description: Update match_event_log table log_seq_no
-- =============================================
ALTER PROCEDURE [dbo].[sp_upd_match_eventlog]
-- Add the parameters for the stored procedure here


AS
declare
@vr_seqno int,
@log_id int

declare log_data cursor for
select log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
order by log_id;

open log_data
fetch next from log_data
into @log_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Update statements for procedure here

BEGIN TRANSACTION Proc_IN

set @vr_seqno = @vr_seqno + 1;

Update [CAMINO].[dbo].[match_event_log]
set log_seqno = @vr_seqno
where log_id = @log_id;

COMMIT TRANSACTION Proc_IN;

END;


besides being slow, it looks to me like when it's all done, the column you want to have sequential integer values will be NULL, because you never assigned a value to @vr_seqno .

offhand i think this would do it in a single shot, but i'm looking for my friend Sean's matching post to be sure:

Update [CAMINO].[dbo].[match_event_log]
set log_seqno = vr_seqno
FROM (select
row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,
log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
) MyData

where log_id = MyData.log_id;



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Just looking at the cursor code itself, it looks like there is a FETCH missing inside the while loop, or did I just miss seeing it?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
npyata
npyata
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
I want to increment vr_seqno by 1. this query will increment vr_seqno?

Update [CAMINO].[dbo].[match_event_log]
set log_seqno = vr_seqno
FROM (select
row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,
log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
) MyData
npyata
npyata
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
Here is the modified code:

DECLARE @log_id INT
DECLARE @vr_seqno INT
DECLARE @getlogid CURSOR
SET @getlogid = CURSOR FOR
SELECT log_id
FROM [testdb].[dbo].[match_event_log]
OPEN @getlogid
FETCH NEXT
FROM @getlogid INTO @log_id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @log_id
set @vr_seqno = @vr_seqno + 1
Update match_event_log
Set log_seqno = @vr_seqno

FETCH NEXT
FROM @getlogid INTO @log_id
END
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Now you are missing a where clause for your update statement. Also, no where do you initialize @vr_seqno, so this value starts as null and stays null as null + 1 = null.

What exactly are you trying to accomplish with this code? Each loop through is going to assign a the value in @vr_seqno to all records where log_id = @log_id (based on your original update statement).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
npyata
npyata
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
I have included the where caluse :
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
order by log_idlog_timestamp_dt between '

I want to increament the log_seqno. So I am using the vr_seqno=@vr_seqno + 1 and updating the log_seqno for each row it is fetched.

Once the above update is done I want to update the other columns based the log_seqno. The update process is running very slow. I am not able to see the results when I terminate the process.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search