SQLServerCentral.com / Development / SQL Server 2005 / CURSOR - running very slow / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:49:46 GMT20RE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxThanksFri, 03 May 2013 08:07:25 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]it working fine now. Let me work on other steps. Thanks to everyone who suggested the code improvement.[/quote]Now the BIG question is, since you're the one that will have to support the code in the future, do you actually understand exactly how the codes works and why?Do you also understand that the word "Cursor" should not be a part of your code 99.99% of the time?Thu, 02 May 2013 20:21:00 GMTJeff ModenRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxit working fine now. Let me work on other steps. Thanks to everyone who suggested the code improvement.Thu, 02 May 2013 12:32:37 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]Here is code which has been suggested: I need the incremental value of log_seqno. How to compute vr_seqno incrementally.ffhand 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;[/quote]Incrementing each rows value is exactly what this is doing. Take a look at ROW_NUMBER. [url=http://msdn.microsoft.com/en-us/library/ms186734.aspx]http://msdn.microsoft.com/en-us/library/ms186734.aspx[/url]If you aren't sure what it is doing just run the select portion.[code]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'[/code]Thu, 02 May 2013 12:20:24 GMTSean LangeRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxHere is code which has been suggested: I need the incremental value of log_seqno. How to compute vr_seqno incrementally.ffhand 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;Thu, 02 May 2013 12:13:39 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]Sean Lange (5/2/2013)[/b][hr][quote][b]npyata (5/2/2013)[/b][hr]Here is the updated code: Still this is running slow.DECLARE @log_id INTDECLARE @vr_seqno INTDECLARE @getlogid CURSORSET @getlogid = CURSOR FORSELECT log_idFROM [testdb].[dbo].[match_event_log]OPEN @getlogidFETCH NEXTFROM @getlogid INTO @log_idWHILE @@FETCH_STATUS = 0set @vr_seqno = 0BEGINPRINT @log_idset @vr_seqno = @vr_seqno + 1Update match_event_logSet log_seqno = @vr_seqnowhere log_id = @log_idFETCH NEXTFROM @getlogid INTO @log_idEND[/quote]It is running slow because you are using a cursor instead a set based update. See the post from Lowell. It will do this in a fraction of the time of this cursor.[/quote]I have to agree. You should try the code Lowell posted earlier.Thu, 02 May 2013 12:04:22 GMTLynn PettisRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxOne question I would ask is, do you care about the order? Your cursor has no order by so there is no guarantee what order the data will be returned. Could you maybe use an identity column?Thu, 02 May 2013 12:01:47 GMTSean LangeRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]Here is the updated code: Still this is running slow.DECLARE @log_id INTDECLARE @vr_seqno INTDECLARE @getlogid CURSORSET @getlogid = CURSOR FORSELECT log_idFROM [testdb].[dbo].[match_event_log]OPEN @getlogidFETCH NEXTFROM @getlogid INTO @log_idWHILE @@FETCH_STATUS = 0set @vr_seqno = 0BEGINPRINT @log_idset @vr_seqno = @vr_seqno + 1Update match_event_logSet log_seqno = @vr_seqnowhere log_id = @log_idFETCH NEXTFROM @getlogid INTO @log_idEND[/quote]It is running slow because you are using a cursor instead a set based update. See the post from Lowell. It will do this in a fraction of the time of this cursor.Thu, 02 May 2013 11:59:02 GMTSean LangeRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxHere is the updated code: Still this is running slow.DECLARE @log_id INTDECLARE @vr_seqno INTDECLARE @getlogid CURSORSET @getlogid = CURSOR FORSELECT log_idFROM [testdb].[dbo].[match_event_log]OPEN @getlogidFETCH NEXTFROM @getlogid INTO @log_idWHILE @@FETCH_STATUS = 0set @vr_seqno = 0BEGINPRINT @log_idset @vr_seqno = @vr_seqno + 1Update match_event_logSet log_seqno = @vr_seqnowhere log_id = @log_idFETCH NEXTFROM @getlogid INTO @log_idENDThu, 02 May 2013 11:53:15 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]Lowell (5/2/2013)[/b][hr]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:[/quote]ROFL Lowell. I was at a meeting at a client's for the last few hours so didn't have a chance to respond. If I had, I think my response would have been almost the identical code you posted. It really is scary!!!Thu, 02 May 2013 11:43:33 GMTSean LangeRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]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.[/quote]Show us. Post the DDL (CREATE TABLE statement) for the table [TESTDB].[dbo].[match_event_log], post some sample data (meaning data you make up, not real production data) as a series of INSERT INTO statements, then show us what the data in the table shold look like when your cursor process is done.Based just on your description, I am not sure what you are doing.Thu, 02 May 2013 10:42:48 GMTLynn PettisRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxI 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.Thu, 02 May 2013 10:32:35 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxNow 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).Thu, 02 May 2013 10:22:33 GMTLynn PettisRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxHere is the modified code:DECLARE @log_id INTDECLARE @vr_seqno INTDECLARE @getlogid CURSORSET @getlogid = CURSOR FORSELECT log_idFROM [testdb].[dbo].[match_event_log]OPEN @getlogidFETCH NEXTFROM @getlogid INTO @log_idWHILE @@FETCH_STATUS = 0BEGINPRINT @log_idset @vr_seqno = @vr_seqno + 1Update match_event_logSet log_seqno = @vr_seqnoFETCH NEXTFROM @getlogid INTO @log_idENDThu, 02 May 2013 10:12:35 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxI 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' ) MyDataThu, 02 May 2013 09:59:37 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxJust 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?Thu, 02 May 2013 09:48:24 GMTLynn PettisRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Author,Narender Pyata-- Create date: 04/30/2013&gt;-- Description: Update match_event_log table log_seq_no-- =============================================ALTER PROCEDURE [dbo].[sp_upd_match_eventlog] -- Add the parameters for the stored procedure hereASdeclare@vr_seqno int,@log_id intdeclare log_data cursor forselect log_idfrom [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_datafetch next from log_data into @log_idWHILE @@FETCH_STATUS = 0BEGIN -- 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;[/quote]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:[code] 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;[/code]Thu, 02 May 2013 09:35:10 GMTLowellRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxHere 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Author,Narender Pyata-- Create date: 04/30/2013&gt;-- Description: Update match_event_log table log_seq_no-- =============================================ALTER PROCEDURE [dbo].[sp_upd_match_eventlog] -- Add the parameters for the stored procedure hereASdeclare@vr_seqno int,@log_id intdeclare log_data cursor forselect log_idfrom [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_datafetch next from log_data into @log_idWHILE @@FETCH_STATUS = 0BEGIN -- 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;Thu, 02 May 2013 09:20:05 GMTnpyataRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspx[quote][b]npyata (5/2/2013)[/b][hr]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[/quote]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.Thu, 02 May 2013 07:41:50 GMTSean LangeRE: CURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxYou need to post the code, otherwise nobody can help you. We might find a way to avoid using a cursor.Thu, 02 May 2013 05:25:46 GMTAndrei HetelCURSOR - running very slowhttp://www.sqlservercentral.com/Forums/Topic1448765-145-1.aspxthis is completedThu, 02 May 2013 05:20:53 GMTnpyata