option other than looping

Question

Post reply

option other than looping

Sha_

Ten Centuries

Points: 1299
More actions
August 17, 2013 at 5:35 pm

#282108

Hi folks,
I'm a procedural thinker, which isn't always good. I need to improve on the runtimes for the below loop. How can this be rewritten so that there is no loop?
Table @loop may have 1000s of rows. Execution time is fine when there is <500 rows or so. Any more than that, and it takes too long to be considered acceptable. It is the loop itself that takes up the time. The xml delete is taking milliseconds per delete.
Would a set-based approach work here? If so, I am not sure how to create one.
Thanks in advance.
while exists (select top 1 * from @loop where done = 0)
begin
select top 1 @X=id
from @loop
where done = 0
select @IssuerID=IssuerName, @IssuerIdentifier=Identifier
from @loop
where @X=id
set @xml.modify('delete /node1/node2/node3[@name=sql:variable("@IssuerID")]');
update @loop
set done = 1
where id = @X
end

Viewing 5 posts - 1 through 4 (of 4 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

If you want to get rid off loops then you can use TALLY table.

Here is the excellent article by Jeff about Tally table, please read it, it will solve your problem:

http://qa.sqlservercentral.com/articles/T-SQL/62867/

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sha_ Ten Centuries Points: 1299 More actions · Answer 2

Thank you for your reply. I took a look at this article and, while useful for certain situations, I don't understand how it would apply to mine.

I need to set a variable, once per row, and using that variable execute the XML DML for each. The "once per row" need may mean that I have no choice but to loop. I wasn't sure not knowing how set-based operations worked.

I believe this means that I cannot use a set-based solution, since it appears that the concept behind set-based is to get multiple rows per - just the opposite of my "execute once per row".

Again, thank you. Unfortunately I am more unclear than before 🙁

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

Sha_ (8/17/2013)
Thank you for your reply. I took a look at this article and, while useful for certain situations, I don't understand how it would apply to mine.
I need to set a variable, once per row, and using that variable execute the XML DML for each. The "once per row" need may mean that I have no choice but to loop. I wasn't sure not knowing how set-based operations worked.
I believe this means that I cannot use a set-based solution, since it appears that the concept behind set-based is to get multiple rows per - just the opposite of my "execute once per row".
Again, thank you. Unfortunately I am more unclear than before 🙁

I don't believe the Tally Table will help here. Some straight forward set-based code likely will but you appear to be missing some information as I see no place where you've defined @Loop or @XML nor any variables for that matter. I realize this is just a snippet but it would be helpful if you posted all of the pieces and some readily consumable data for us to test our solutions with. Please see the first link in my signature line below for the proper way to do that to get the best help.

--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)
Intro to Tally Tables and Functions

below86 SSChampion Points: 11418 More actions · Answer 4

When I tried something similar awhile ago I found it was faster to delete the records from the 'temp' table as I went. I would think this would do the same thing you had if you don't need the @loop table after this step.

while exists (select top 1 * from @loop )--where done = 0)

begin

select top 1 @X=id, @IssuerID=IssuerName, @IssuerIdentifier=Identifier

from @loop

--where done = 0

--select @IssuerID=IssuerName, @IssuerIdentifier=Identifier

--from @loop

--where @X=id

set @xml.modify('delete /node1/node2/node3[@name=sql:variable("@IssuerID")]');

--update @loop

--set done = 1

delete

from @loop

where id = @X

end

I would also try and narrow down which step is taking the most time. I like to add the following between my steps to try and figure out which step is causing the problem. Be sure to set 'results to text' when running in SSMS.

SELECT 'Step 00300 finished at' , convert(char(26), CURRENT_TIMESTAMP, 121);

Change the 'step' number between each one. I like to go in increments of 100 in case i need to add steps between.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.