May 2, 2013 at 5:20 am
this is completed
May 2, 2013 at 5:25 am
You need to post the code, otherwise nobody can help you. We might find a way to avoid using a cursor.
May 2, 2013 at 7:41 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 9:20 am
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;
May 2, 2013 at 9:35 am
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
May 2, 2013 at 9:48 am
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?
May 2, 2013 at 9:59 am
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
May 2, 2013 at 10:12 am
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
May 2, 2013 at 10:22 am
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).
May 2, 2013 at 10:32 am
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.
May 2, 2013 at 10:42 am
npyata (5/2/2013)
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.
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.
May 2, 2013 at 11:43 am
Lowell (5/2/2013)
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:
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!!!
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 11:53 am
Here is the updated code: Still this is running slow.
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
set @vr_seqno = 0
BEGIN
PRINT @log_id
set @vr_seqno = @vr_seqno + 1
Update match_event_log
Set log_seqno = @vr_seqno
where log_id = @log_id
FETCH NEXT
FROM @getlogid INTO @log_id
END
May 2, 2013 at 11:59 am
npyata (5/2/2013)
Here is the updated code: Still this is running slow.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
set @vr_seqno = 0
BEGIN
PRINT @log_id
set @vr_seqno = @vr_seqno + 1
Update match_event_log
Set log_seqno = @vr_seqno
where log_id = @log_id
FETCH NEXT
FROM @getlogid INTO @log_id
END
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.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2013 at 12:01 pm
One 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?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply