Need help with a more efficient cursor 2

[cfw2]

I am trying to run the below cursor to sequentially number the records for each "rectype" in a transaction each starting at 1. The problem is this took 35 minutes to run on my sample of 46,000 records. I could potentially have over a million at a time in production.

Thank You in advance for your help!!!

	 declare @transdate smalldatetime
	 declare @storenum int
	 declare @regnum int
	 declare @transnum int
	 declare @rectype char(3)
	 declare @vn_loop_count int
	 declare @vn_rowcount int

 declare c_trans_info cursor for
	select distinct 
	 transdate
	 ,storenum
	 ,regnum
	 ,transnum
	 ,rectype
	from info_report
	order by 1, 2, 3, 4, 5

  open c_trans_info
	fetch c_trans_info into @transdate, @storenum, @regnum, @transnum, @rectype
	while ( @@fetch_status <> -1 )

	begin
	  set @vn_loop_count = 1
	  set @vn_rowcount = 1
	  set rowcount 1

	  while @vn_rowcount = 1
		begin 
		  update info_report
		  set rectype_id = @vn_loop_count
		  where transdate = @transdate
		    and storenum = @storenum
		    and regnum = @regnum
		    and transnum = @transnum
		    and rectype = @rectype
		    and rectype_id is null
		  select @vn_rowcount = @@rowcount
		  select @vn_loop_count = @vn_loop_count + 1
		end

	  set rowcount 0
	fetch c_trans_info into @transdate, @storenum, @regnum, @transnum, @rectype
	end
  deallocate c_trans_info
set rowcount 0

 

[gmmastros]

You can probably do this in a set based way, which would have HUGE speed improvements over your current method. So that we can understand your question a little better, can you please post some sample data and expected results?

This will help us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

This should work (in theory anyway ;-))

    select distinct 
     transdate
     ,storenum
     ,regnum
     ,transnum
     ,rectype,IDENTITY(int, 1,1) AS ID_Num
    INTO tmp_info_report
	from info_report
    order by 1, 2, 3, 4, 5

 
update i 
set i.rectype_id =  i2.ID_Num
from info_report i 
join tmp_info_report i2 on
i.transdate = i2.transdate
and i.storenum = i2.storenum
and i.regnum = i2.regnum
and i.transnum = i2.transnum
and i.rectype = i2.rectype
--and i.rectype_id is null -- not sure about this

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cfw2]

SQLDennis, I do already have an identity column in there. I am trying to make an identity-like column, but it needs to start over for each rectype. So for example, my table looks like this:

pk_identity	trans_id	rectype	rectype_id
1	1	HDR	NULL
2	1	HDR	NULL
3	1	TND	NULL
4	1	TND	NULL
5	5	HDR	NULL
6	5	HDR	NULL
7	5	TND	NULL
8	5	TND	NULL
9	9	HDR	NULL

I want it to look like this:

pk_identity	trans_id	rectype	rectype_id
1	1	HDR	1
2	1	HDR	2
3	1	TND	1
4	1	TND	2
5	5	HDR	1
6	5	HDR	2
7	5	TND	1
8	5	TND	2
9	9	HDR	1

 

[TysonLPrice]

If RecType is all you are concerned with run the for each one:

declare @updateValue int
SET @updateValue = 0
update info_report set rectype_id =
@UpdateValue ,@updateValue = @updateValue + 1
where rectype = --Whatever
and rectype_id is null

Probably would take more the a minute or so for all of them.

 

[SQLDenis]

and if you use SQL 2005 you can use RANK()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ESquared]

UPDATE info_report
SET rectype_id = 1 + (SELECT Count(*) FROM info_report R where R.trans_id = trans_id AND R.rectype = rectype AND R.pk_identity < pk_identity)

or

UPDATE R
SET R.rectype_id = Cnt
FROM
   info_report R
   INNER JOIN (
      SELECT
         R1.pk_identity,
         Cnt = Count(*)
      FROM
         info_report R1
         INNER JOIN info_report R2 ON
            R1.trans_id = R2.trans_id
            AND R1.rectype = R2.rectype
            AND R1.pk_identity >= R2.pk_identity
      GROUP BY R1.pk_identity
   ) X ON R.pk_identity = X.pk_identity

These will perform differently on different size data sets. You should try them against real-world data and data sizes to see which performs better.

And as SQLDenis says, in SQL Server 2005 there are other options available to you.

 

[cfw2]

Perfect!

SQL Dennis, I am going to look into the RANK(), as I am using SQL 2005, but only for 1 server.

ESquared, your second option works so much better than my cursor! Thanks... your first option gave me the result of 1 for every record.

Thank You All

 

[ESquared]

Oh, I see what I did wrong. I got the aliases mixed up.

UPDATE X
SET rectype_id = 1 + (SELECT Count(*) FROM info_report R where R.trans_id = X.trans_id AND R.rectype = X.rectype AND R.pk_identity < X.pk_identity)
FROM info_report X

 

[cfw2]

that did it... that one is actually faster than the second option you originally gave too. Thanks again, Charles