I want optimize the store procedure-It's take more time to excecute by use while loop

Question

Post reply

I want optimize the store procedure-It's take more time to excecute by use while loop

s.chandrahasan

SSC Eights!

Points: 858
More actions
September 15, 2010 at 5:39 am

#227782

Hi friends,
Kindly check my store procedure and advise me why it's taking log time to excecute ,
and i want avoid while loops ,how can i do it ,kindly help me....pleaseeeeeeeeeee
SP ::
while @cnt<=@cntmax
Begin
select @cno=cno from @dgclassunique where id =@cnt
select @bno=bno from @dgclassunique where id =@cnt
select @flag=flag from @dgclassunique where id =@cnt
SET @dgstr=''
select @dgstr = @dgstr +substring(dgclass,0,len(dgclass))+' , ' from @dgclassunique where cno=@cno and bno=@bno and flag=@flag
SET @dgstr=substring(@dgstr,0,len(@dgstr))
insert into #dgclassNew (eqpid,book_no,flag,dgclass) values(@cno,@bno,@flag,@dgstr)
set @cnt=@cnt+1
End

Viewing 15 posts - 1 through 15 (of 15 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

It's really inefficient code. Try this:

INSERT INTO #dgclassNew (eqpid, book_no, flag, dgclass)

SELECT d.cno, d.bno, d.flag, iTVF.dgclass

FROM @dgclassunique d

CROSS APPLY

(SELECT CAST(dgclass AS VARCHAR) + ','

FROM @dgclassunique

WHERE cno = d.cno

AND bno = d.bno

AND flag = d.flag

FOR XML PATH('')) iTVF (dgclass)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 2

Hi ,

I applied ur suggestion it's nice

can u please check below one and advise me

how to avoid while loop in this below case :

SP:

-----------------------

Declare @BLconc varchar(20)

Declare @BLbnoc varchar(20)

Declare @BLCHstrs varchar(5000)

declare @TEMPBLCommodityunique Table(id int identity(1,1),BLconc varchar(20),BLbnoc varchar(20),cname varchar(1000) )

insert into @TEMPBLCommodityunique(BLconc,BLbnoc,cname) select distinct EqpId , Book_No,cname from #TEMPBLCommodity (NoLock)

select @cntmax=max(id) from @TEMPBLCommodityunique

set @cnt=1

while @cnt<=@cntmax

Begin

select @BLconc=BLconc from @TEMPBLCommodityunique where id=@cnt

select @BLbnoc=BLbnoc from @TEMPBLCommodityunique where id=@cnt

SET @BLCHstrs = ''

select @BLCHstrs = @BLCHstrs +CName+' , ' from @TEMPBLCommodityunique where BLbnoc=@BLbnoc and BLconc=@BLconc

SET @BLCHstrs = substring(@BLCHstrs,0,len(@BLCHstrs))

insert into #TEMPBLCommodityNew (EqpId,book_no,CName) values(@BLconc,@BLbnoc,@BLCHstrs)

set @cnt=@cnt+1

End

--------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

s.chandrahasan (9/16/2010)
can u please check below one and advise me
how to avoid while loop in this below case :

Sure.

This set of statements performs the same action as your original post - to concatenate the values from several rows into one single value on one row.

Should be a straightforward job to change the table name and column names in the sample code I posted.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 4

Hi ,

first many thanks for ur reply !!!

I tried but i can't do ,

can u please post again your code for my updated code ???

i dont want use while loop ,so i want any alternate solution for while loop please check and reply me

Thanks,

Chandrahasan S

sanmatrix SSC Veteran Points: 250 More actions · Answer 5

If it's not urgent then I would suggest try it on your own. You have the code and the logic as well

If you don't understant part of the code then go through MSDN or just google it...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

s.chandrahasan (9/16/2010)
Hi ,
first many thanks for ur reply !!!
I tried but i can't do ,
can u please post again your code for my updated code ???
i dont want use while loop ,so i want any alternate solution for while loop please check and reply me
Thanks,
Chandrahasan S

It's important that you understand how the code works so that you can safely and confidently integrate it with your existing code. Please try again. If you still experience difficulty, then post what you have done so your work can be corrected with explanations.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 7

Hi,

i dont know what is this line can u explain

FOR XML PATH('')) iTVF (dgclass)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

s.chandrahasan (9/17/2010)
Hi,
i dont know what is this line can u explain
FOR XML PATH('')) iTVF (dgclass)

There are two concepts here, xml handling (which generates the concatenated string from a bunch of rows) and APPLY.

xml handling is explained very well here http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

APPLY is explained with exceptional clarity by Paul White in the article links in my sig, below. iTVF is an acronym for inline table-valued function and is a tablesource alias.

Try experimenting with this part;

DECLARE @cno INT, @bno INT, @flag INT

-- ALSO, set up a @dgclassunique table with a restricted number of rows

SELECT CAST(dgclass AS VARCHAR) + ','

FROM @dgclassunique

--WHERE cno = @cno

--AND bno = @bno

--AND flag = @flag

FOR XML PATH('')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 9

This link explains FOR XML PATH:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Paul White NZ (9/17/2010)
This link explains FOR XML PATH:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Thanks for the link, Paul - it's a much better fit for the OP's requirements than the link I posted. Nice one.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 11

Chris Morris-439714 (9/17/2010)
Thanks for the link, Paul - it's a much better fit for the OP's requirements than the link I posted. Nice one.

No worries, Chris. I think we posted together, and gave a good answer between us 🙂

Thanks also for the APPLY article plug!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 12

Dear All,

Kindly advise me is it correct ,1st part while used data , Part 2 newly i created

if it is anything wrong in my coding please advise ...

Thanks for your reply..:-)

Part 1:

Declare @BLconc varchar(20)

Declare @BLbnoc varchar(20)

Declare @BLCHstrs varchar(5000)

declare @TEMPBLCommodityunique Table(id int identity(1,1),BLconc varchar(20),BLbnoc varchar(20),cname varchar(1000) )

insert into @TEMPBLCommodityunique(BLconc,BLbnoc,cname) select distinct EqpId , Book_No,cname from #TEMPBLCommodity (NoLock)

select @cntmax=max(id) from @TEMPBLCommodityunique

set @cnt=1

while @cnt<=@cntmax

Begin

select @BLconc=BLconc from @TEMPBLCommodityunique where id=@cnt

select @BLbnoc=BLbnoc from @TEMPBLCommodityunique where id=@cnt

SET @BLCHstrs = ''

select @BLCHstrs = @BLCHstrs +CName+' , ' from @TEMPBLCommodityunique where BLbnoc=@BLbnoc and BLconc=@BLconc

SET @BLCHstrs = substring(@BLCHstrs,0,len(@BLCHstrs))

insert into #TEMPBLCommodityNew (EqpId,book_no,CName) values(@BLconc,@BLbnoc,@BLCHstrs)

set @cnt=@cnt+1

End

---------------------------------------------------------------------------------

--------------------------------------------------------------------------------

I developed coding

Part 2:

insert into #TEMPBLCommodityNew (EqpId,book_no,CName)

select d.BLconc,b.BLbnoc,iTVF.CName

from @TEMPBLCommodityunique d

CROSS APPLY

(SELECT CAST(CName AS VARCHAR) + ','

FROM @TEMPBLCommodityunique

WHERE BLconc=d.BLconc

and BLbnoc=c.BLbnoc

FOR XML PATH('')) iTVF (CName)

-----------------------------------------

-------------------------------------------

Kindly advise if anything wrong in my changes

Thanks & Regards,

Chandrahasan

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 13

Dear All,

I have some doubts please clarify,

I have used two variable to divide by using "," in while loop .

how to implement in your query

------

while loop query

Declare @blno varchar(20)

Declare @bltype varchar(20)

Declare @blnostr varchar(max)

Declare @bltypestr varchar(max)

declare @blnumbertypeunique TABLE(id int identity(1,1),bno varchar(20),cno varchar(20))

insert into @blnumbertypeunique(bno,cno)select distinct Book_no,Eqpid from #TEMPBLnoTypeNtwt

select @cntmax=max(id) from @blnumbertypeunique

set @cnt=1

while @cnt<=@cntmax

Begin

select @bno=bno from @blnumbertypeunique where id =@cnt

select @cno=cno from @blnumbertypeunique where id =@cnt

SET @blnostr=''

SET @bltypestr=''

select @blnostr = @blnostr + substring(blno,0,len(blno)+1)+' , ', @bltypestr = @bltypestr +substring(bltype,0,len(bltype)+1)+' , '

from #TEMPBLnoTypeNtwt where Book_no=@bno and Eqpid=@cno

SET @blnostr=substring(@blnostr,0,len(@blnostr))

SET @bltypestr=substring(@bltypestr,0,len(@bltypestr))

insert into #TEMPBLNoType (book_no,eqpid,blno,bltype) values(@bno,@cno,@blnostr,@bltypestr)

set @cnt=@cnt+1

End

-----------------------

I did

Please correct it in my query

when i execute this shows error

query

declare @blnumbertypeunique TABLE(id int identity(1,1),bno varchar(20),cno varchar(20),blnostr varchar(max),bltypestr varchar(max))

insert into @blnumbertypeunique(bno,cno)select distinct Book_no,Eqpid from #TEMPBLnoTypeNtwt

insert into #TEMPBLNoType (book_no,eqpid,blno,bltype)

select d.bno,d.cno,iTVF.blnostr,iTVF.bltypestr

from @blnumbertypeunique d

CROSS APPLY

(SELECT CAST(blnostr AS VARCHAR) + ',' ,SELECT CAST(bltypestr AS VARCHAR) + ','

FROM @blnumbertypeunique

WHERE bno=d.bno

and cno=d.cno

FOR XML PATH('')) iTVF (blnostr)

FOR XML PATH('')) iTVF (bltypestr)

Error

Msg 156, Level 15, State 1, Line 127

Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Line 131

Incorrect syntax near ')'.

kindly advise me

Thanks,

Chandrahasan

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 14

Hi,

i need urgent can you please tell me

how can i separate with "," for two columns

in while loop i used

select @blnostr = @blnostr + substring(blno,0,len(blno)+1)+' , ', @bltypestr = @bltypestr +substring(bltype,0,len(bltype)+1)+' , '

from #TEMPBLnoTypeNtwt where Book_no=@bno and Eqpid=@cno

I want in cross apply format

CROSS APPLY

(SELECT CAST(blnostr AS VARCHAR) + ',' ,CAST(blnostr AS VARCHAR) + ','

FROM @blnumbertypeunique

WHERE bno=d.bno

and cno=d.cno

FOR XML PATH('')) iTVF (blnostr,bltypestr)

please advise

Thanks,

Chandrahasan