How to minimize processing time when using a cursor

Question

Post reply

How to minimize processing time when using a cursor

Kicking_Fish0713

SSC Eights!

Points: 896
More actions
November 19, 2008 at 8:20 pm

#196096

Hi,
Is there a way I could minimize the processing time of my below query?
I'm using cursor to transpose values from columns into rows.
Before update:
Number: Code: Values: Code_0001 Code_002 Code_003
123450001.0000000NULL NULL NULL
123450002.1500000NULL NULL NULL
1234500032.4600000NULL NULL NULL
123450004.1600000NULL NULL NULL
123450005.0900000NULL NULL NULL
Expected result:
Number: Code: Values: Code_0001 Code_002 Code_003
123450001.0000000.0000000 .1500000 2.4600000
123450002.1500000NULL NULL NULL
1234500032.4600000NULL NULL NULL
123450004.1600000NULL NULL NULL
123450005.0900000NULL NULL NULL
What I got is this:
Number: Code: Values: Code_0001 Code_002 Code_003
123450001.0000000.0000000 .1500000 2.4600000
123450002.1500000.0000000 .1500000 2.4600000
1234500032.4600000.0000000 .1500000 2.4600000
123450004.1600000.0000000 .1500000 2.4600000
123450005.0900000.0000000 .1500000 2.4600000
I'm using SQL2000.
Here's my script:
/* -------------------------
Transpose CT values from column to row
--------------- */
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'##tmpP3TestWIP_MFG_AssyILN_STNa')
AND OBJECTPROPERTY(id,N'IsTable') = 1) DROP TABLE ##tmpP3TestWIP_MFG_AssyILN_STNa
SELECT *,identity(int) as icount
INTO ##tmpP3TestWIP_MFG_AssyILN_STNa
FROM ##tmpP3TestWIP_MFG_AssyILN_STN
create clustered index ##tmpP3TestWIP_MFG_AssyILN_STNa on ##tmpP3TestWIP_MFG_AssyILN_STN (Internal_Lot_Number,Station,Cycle_Time_perStation)
DECLARE @AssyILNctr1 numeric(6),@AssyILNctr2 numeric(6),@AssyILNILN2 varchar(30),@AssyILNStation2 varchar(4),@AssyILNValue numeric(21,7),@AssyILNStrSql2 nvarchar(1000)
SET @AssyILNctr1 = (SELECT MAX(icount) FROM ##tmpP3TestWIP_MFG_AssyILN_STNa)
SET @AssyILNctr2 = 1
while @AssyILNctr2 <= @AssyILNctr1
begin
-- SELECT * FROM ##tmpP3TestWIP_MFG_AssyILN_STNa WHERE icount = @ctr2
SET @AssyILNStation2 = (SELECT DISTINCT Station FROM ##tmpP3TestWIP_MFG_AssyILN_STNa WHERE icount = @AssyILNctr2)
SET @AssyILNValue = (SELECT DISTINCT Cycle_Time_perStation FROM ##tmpP3TestWIP_MFG_AssyILN_STNa WHERE icount = @AssyILNctr2)
SET @AssyILNILN2 = (SELECT DISTINCT Internal_Lot_Number FROM ##tmpP3TestWIP_MFG_AssyILN_STNa WHERE icount = @AssyILNctr2)
SET @AssyILNStrSql2 = 'UPDATE ##tmpP3TestWIP_MFG_AssyILN_STNa
SET STN_' + @AssyILNStation2 + ' = ' + convert(varchar(11),@AssyILNValue) +
' WHERE Internal_Lot_Number = ' + '''' + @AssyILNILN2 + ''''
EXEC sp_executesql @AssyILNStrSql2
-- print @AssyILNStrSql2
-- print @AssyILNStation2
-- print @AssyILNValue
SET @AssyILNctr2 = @AssyILNctr2 + 1
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Hopefully the following test code will help you figure out what you need:

create table #MyTest (

Number int,

Code char(4),

CodeValues decimal(10,7),

Code_0001 decimal(10,7) null,

Code_0002 decimal(10,7) null,

Code_0003 decimal(10,7) null

)

insert into #MyTest (Number, Code, CodeValues)

select 12345,'0001',.0000000 union all

select 12345,'0002',.1500000 union all

select 12345,'0003',2.4600000 union all

select 12345,'0004',.1600000 union all

select 12345,'0005',.0900000

select * from #MyTest -- check entry of data

select

Number,

min(Code),

sum(case when Code = '0001' then CodeValues else 0.00 end) as Code_0001,

sum(case when Code = '0002' then CodeValues else 0.00 end) as Code_0002,

sum(case when Code = '0003' then CodeValues else 0.00 end) as Code_0003

from

#MyTest

group by

Number

update #MyTest set

Code_0001 = dt.Code_0001,

Code_0002 = dt.Code_0002,

Code_0003 = dt.Code_0003

from

#MyTest mt

inner join (select

Number,

min(Code) as Code,

sum(case when Code = '0001' then CodeValues else 0.00 end) as Code_0001,

sum(case when Code = '0002' then CodeValues else 0.00 end) as Code_0002,

sum(case when Code = '0003' then CodeValues else 0.00 end) as Code_0003

from

#MyTest

group by

Number) dt

on (mt.Number = dt.Number

and mt.Code = dt.Code)

select * from #MyTest -- recheck data

drop table #MyTest -- drop the test table

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 2

Thanks for a quick response.I don't use hardcoded values that's why I'm using a cursor.Can you pls send me a sample script using a cursor?

Thanks in advance.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

You already have one. Any cursor code is going to be slow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Kicking_Fish0713 (11/19/2008)
Thanks for a quick response.I don't use hardcoded values that's why I'm using a cursor.Can you pls send me a sample script using a cursor?
Thanks in advance.

First of all, you aren't using a cursor in your sample code, and if you are, I can't find it.

Two, I can't read your code due to the poor formatting of the code, and I really don't feel like taking the time to reformat it to try and understand it.

Third, please explain why the code provided doesn't meet your requirements. Based on your post, it does. A better explaination of the problem, and additional test data and expected results would probably help.

You may also want to read the article that I have linked in my signature block for more information on how to ask for help, as I did have to take the time to properly format your test data and write the create statement for the test table. All of which I provided back to you in my post.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

Lynn Pettis (11/20/2008)
Kicking_Fish0713 (11/19/2008)
Thanks for a quick response.I don't use hardcoded values that's why I'm using a cursor.Can you pls send me a sample script using a cursor?
Thanks in advance.
First of all, you aren't using a cursor in your sample code, and if you are, I can't find it.

Might as well be...

while @AssyILNctr2 <= @AssyILNctr1

begin

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (11/20/2008)
Lynn Pettis (11/20/2008)
Kicking_Fish0713 (11/19/2008)
Thanks for a quick response.I don't use hardcoded values that's why I'm using a cursor.Can you pls send me a sample script using a cursor?
Thanks in advance.
First of all, you aren't using a cursor in your sample code, and if you are, I can't find it.
Might as well be...
while @AssyILNctr2 <= @AssyILNctr1
begin

I guess I'm too literal, as I was looking for the word cursor some where in the code and couldn't find it. Good catch!;)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Heh... yeah... I don't even look for the word CURSOR, anymore... they need a WHILE loop to really do anything and, since both are RBAR, I just look for the WHILE now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

That makes sense. I guess I'll start following that lead as well.