My goal was to run a procedures feeding it a parameter from an input table, executing it once for each record. Can you give me advice on how to improve it? I came up with this:
begin
declare @Vertrag as int = 1
set @Vertrag = (select min(ref_nr_vertrag)
from tbl_111_aed2_umhaengen_Verträge)
while @Vertrag <= -- do while contract number is lower than the highest
(select max(ref_nr_vertrag) from tbl_111_aed2_umhaengen_Verträge)
begin -- While
exec usp_PLP_und_PRP_Aktualisierung_ASAP 0, 2020, '%', @vertrag
set @Vertrag = -- set to next highest number
(select coalesce(min(ref_nr_vertrag), 9999999) -- exit clause, no contract numbers higher than 2020999 by definition
from tbl_111_aed2_umhaengen_Verträge
where ref_nr_vertrag > @Vertrag)
print 'Update contract Nr:' + cast(@vertrag as nvarchar(7))
end --While
end -- procedure
thank you for any suggestions.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
November 11, 2019 at 9:31 am
Why does it need to be improved - does it not work?
I would start by making sure you have the same number of ENDs as BEGINs. Then I would consider rewriting usp_PLP_und_PRP_Aktualisierung_ASAP so that it processes all the rows in one go, not one at a time. Without being able to see that stored procedure, though, it's difficult to advise.
John
November 11, 2019 at 9:40 am
Thanks for your answer. I just thought, maybe there is more elegant way to do something like this, I started writing T-SQL only a year ago and am eager to learn. The stored procedure can be be run in one go (I omitted the last parameter for simplicity, it is a range). But we (would) have to rewrite it in the future with table locks and/or transctions ... but that's another story.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
November 11, 2019 at 11:20 am
As a general rule of thumb, you want to avoid looping.
So, I would be looking to rewrite the proc that is being called from the loop to process the data in batches
November 11, 2019 at 11:47 am
thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
November 11, 2019 at 1:19 pm
thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.
Probably not what was meant. Before MS bastardized the meaning of what a lot of people meant as "batch mode", it meant the opposite of doing things row by row in a loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2019 at 1:21 pm
p.s. You might want to post the stored procedure and the contents (or a least a sample of) the control table. See the first link in my signature line below for how to do the last part of that in the best way to help us help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2019 at 2:06 pm
Thanks for your suggestions. I think the procedure is not my main problem, because it allows to update records , for the whole company for one year, for a cost unit, for a range of contracts, for budgets, actual cost or both and any combination. But because it is the main table also used for reporting we run into nasty problems where the delete part would be executed, but not the insert part ... which meant the budget for 2020 was empty until we could run the procedure again. We scared quite some people this way. I must that we are amateurs (read your quote), learning by doing, and have to (re-)program and update several programs from database design up to user interface ... magically it works and I even got a promotion 😉 The procedure calls 4 (sub)procedures which delete and append to different tables. I guess it would be too much to post all 5 procedures and extracts from the 4 tables they get data from or delete and insert into.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
Your query would be more efficient if you used a cursor to select distinct ref_nr_vertrag from table tbl_111_aed2_umhaengen_Verträge. Like this:
DECLARE @Vertrag AS int= 1
DECLARE @myCursor cursor
SET @myCursor = cursor FOR SELECT DISTINCT ref_nr_vertrag FROM tbl_111_aed2_umhaengen_Verträge
OPEN @myCursor
FETCH NEXT FROM @myCursor INTO @Vertrag
WHILE @@FETCH_STATUS = 0 BEGIN
exec usp_PLP_und_PRP_Aktualisierung_ASAP 0, 2020, '%', @vertrag
FETCH NEXT FROM @myCursor INTO @Vertrag
print 'Update contract Nr:' + cast(@vertrag as nvarchar(7))
END
CLOSE @myCursor
DEALLOCATE @myCursor
Without seeing the code in the stored procedure, or what it does, it's not possible to say, but you might be able to rewrite the stored procedure as a function. In which case you could call it with one SQL statement using CROSS APPLY.
;WITH CTE AS
(
SELECT DISTINCT ref_nr_vertrag
FROM tbl_111_aed2_umhaengen_Verträge
)
SELECT *
FROM CTE
CROSS APPLY func_PLP_und_PRP_Aktualisierung_ASAP (0, 2020, '%', CTE.ref_nr_vertrag)
November 11, 2019 at 2:50 pm
thanks. looks good. I will look into this soon.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
November 14, 2019 at 6:04 pm
A function cannot do an UPDATE. But for just retrieving data, I agree with the cross apply approach.
What Jeff and others are trying to explain is that if your procedure just updates one row at a time, it can be improved. A source table can be joined to a target table to update many rows all at once. This is called "set based processing" and is generally faster than looping to call a procedure multiple times, updating each row one by one. It is also easier and involves less code once you get the knack of it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 14, 2019 at 10:06 pm
christoph.kauflin wrote:thanks for your answer. I googled batch mode and I guess I need some more experience and knowledge before I'd be able to transform my code into batch mode.
Probably not what was meant. Before MS bastardized the meaning of what a lot of people meant as "batch mode", it meant the opposite of doing things row by row in a loop.
In my experience its always been sort of the opposite of "interactive", and CERTAINLY batch jobs did not prevent loops.
https://en.wikipedia.org/wiki/Batch_processing
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply