September 22, 2009 at 1:24 pm
Is this possible? I know I can write a script which will loop through each record in the query result set, and execute the stored proc against each record, like for example:
CREATE TABLE #TempTable
(
ID INT PRIMARY KEY IDENTITY,
COLUMNS Types
)
INSERT INTO #TempTable
SelectStatement FROM Table
DECLARE @CurrID INT
DECLARE @NewID INT
SET @CurrID = 0
SET @NewID = 0
WHILE 1 = 1
BEGIN
SELECT TOP 1 @NewID = ID, Values
FROM #TempTable
WHERE ID > @CurrID
ORDER BY ID
IF @NewID = @CurrID BREAK
EXEC sp_SomeProc @NewID
SET @CurrID = @NewID
END
But, is it possible to be able to do this in a simpler manner, since I have no logic at all that I need to implement? All I want to do is execute the stored proc for each record in the query resultset.
September 22, 2009 at 3:02 pm
i would do something like this
DECLARE @MAX_ID int, @CONS int, @CURRID int
SELECT @MAX_ID=0, @CONS=1
SELECT @MAX_ID=COUNT(*)
FROM YOUR_TABLE
WHILE @CONS<=@MAX_ID
BEGIN
SELECT @CURRID=ID
from (
select ROW_NUMBER() OVER (order by ID desc) ROWNUMBER,*
from YOUR_TABLE
) a
where ROWNUMBER=@CONS
if @CURRID is not null
EXEC YOUR_PROCEDURE @CURRID
SET @CONS=@CONS+1
END
September 22, 2009 at 3:12 pm
Fausto Echevarria (9/22/2009)
i would do something like thisDECLARE @MAX_ID int, @CONS int, @CURRID int
SELECT @MAX_ID=0, @CONS=1
SELECT @MAX_ID=COUNT(*)
FROM YOUR_TABLE
WHILE @CONS<=@MAX_ID
BEGIN
SELECT @CURRID=ID
from (
select ROW_NUMBER() OVER (order by ID desc) ROWNUMBER,*
from YOUR_TABLE
) a
where ROWNUMBER=@CONS
if @CURRID is not null
EXEC YOUR_PROCEDURE @CURRID
SET @CONS=@CONS+1
END
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 22, 2009 at 3:25 pm
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
i know this is the worst option if you are doing lots of inserts. I i dont know what the inner stored procedure is doing, but some times, you got no option but to do something like that, and i guess thats the best way to do it,insted of using a cursor.
September 22, 2009 at 3:51 pm
Fausto Echevarria (9/22/2009)
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
i know this is the worst option if you are doing lots of inserts. I i dont know what the inner stored procedure is doing, but some times, you got no option but to do something like that, and i guess thats the best way to do it,insted of using a cursor.
It's functionally equivalent to a cursor. The WHILE loop really will not buy you any perf advantage here. That said - pulling things out in descreasing row order will get really screwed up once someone inserts a new row, since your ROW_NUMBER() is going to be pointing to a different row than what it was before the insert happened (which is not a problem you would have had with the cursor). So at best - you hit one (or more) row twice, and you miss one (or more) rows that used to be in the pack beforehand.
I was just hoping our OP would tell us what he's trying to accomplish. Very likely there is a better way to go than this.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 22, 2009 at 3:58 pm
Matt Miller (#4) (9/22/2009)
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
Well, part of this is theoretical, since I run upon this type of situation fairly often. In this particular case it may be possible to modify the inner stored proc to handle all the work against a group of records, rather than against one record.
But the general idea is to do an "upsert" - insert if the record isn't found, update if the record is found. What I could probably do is split the batch into two - one batch which will insert, and the other batch which will update.
However, if the situation was such that it was simply not possible, and that it had to be done such that a stored proc or batch of code was executed for each record, is the solution which I posted, or variations of it, the only option?
September 22, 2009 at 4:00 pm
It's functionally equivalent to a cursor. The WHILE loop really will not buy you any perf advantage here. That said - pulling things out in descreasing row order will get really screwed up once someone inserts a new row, since your ROW_NUMBER() is going to be pointing to a different row than what it was before the insert happened (which is not a problem you would have had with the cursor). So at best - you hit one (or more) row twice, and you miss one (or more) rows that used to be in the pack beforehand.
I was just hoping our OP would tell us what he's trying to accomplish. Very likely there is a better way to go than this.
yeah, again, you are right :-D... and yes probably theres a better way to go... we gotta wait til we know more bout this issue
September 22, 2009 at 6:02 pm
kramaswamy (9/22/2009)
Matt Miller (#4) (9/22/2009)
This is bound to go *boom* if ANYONE is doing inserts on the table while you're running this loop.
What is the inner stored procedure doing? This approach tends to be about the worse you can get in terms of performance, since you're forcing SQL Server to work one row at a time. SQL Server is a "set engine" meaning it performs best against a LOT of records at the same time, so throttling it like this is kind of like buying a porsche, and just using it for the ashtray.
Well, part of this is theoretical, since I run upon this type of situation fairly often. In this particular case it may be possible to modify the inner stored proc to handle all the work against a group of records, rather than against one record.
But the general idea is to do an "upsert" - insert if the record isn't found, update if the record is found. What I could probably do is split the batch into two - one batch which will insert, and the other batch which will update.
However, if the situation was such that it was simply not possible, and that it had to be done such that a stored proc or batch of code was executed for each record, is the solution which I posted, or variations of it, the only option?
The concept of an upsert is common, and like you mentioned - is functionally broken up into 2 steps. It is however rather easy to make sure that BOTH happen successfully using TRY/CATCH and transaction handling (IMO easier than trying to ensure the same in a lookp or cursor scenario).
As to what to do "if it's not possible", then a while loop, a "for loop" (i.e. the WHILE loop implemented as you have it) or a cursor will all essentially be the same. That said - there really are not a lot of scenarios where any of those options will be better than a set-based solution, unless you're dealing with an outside application or maintenance tasks.
When you get stuck with one of those cases, quite frankly your best bet is to pose the scenario up here. You'd be amazed at the options you will get, and most of the good ones will not be cursor/loop-based.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply