Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

CURSOR - running very slow Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 5:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
Points: 9, Visits: 28
this is completed
Post #1448765
Posted Thursday, May 2, 2013 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 12:25 PM
Points: 40, Visits: 143
You need to post the code, otherwise nobody can help you. We might find a way to avoid using a cursor.
Post #1448767
Posted Thursday, May 2, 2013 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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)
Post #1448814
Posted Thursday, May 2, 2013 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
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;
Post #1448858
Posted Thursday, May 2, 2013 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448861
Posted Thursday, May 2, 2013 9:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
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?



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)
Post #1448872
Posted Thursday, May 2, 2013 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
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

Post #1448883
Posted Thursday, May 2, 2013 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
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
Post #1448895
Posted Thursday, May 2, 2013 10:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
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).



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)
Post #1448899
Posted Thursday, May 2, 2013 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
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.
Post #1448907
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse