Hey SSC,
Please if we have a stored procedure in which at a time only a single code segment of IF is executed out of many IFs in the procedure.
Every IF code segment has a different query and execution time.
So on cumulative what info would be sys.dm_exec_procedure_stat will bring to us? and how to gauge it for all IFs or what strategy to check if any optimization has done something with either IF clause code segment?
Thanks,
November 3, 2020 at 12:21 pm
Divide and conquer.
Put all the actual statements to execute into separate procedures and make the main procedure call some of them according to the flow control logic.
if @a = 0
exec subprocedure0
if @a =1
exec subrocedure1
etc.
_____________
Code for TallyGenerator
November 3, 2020 at 4:57 pm
The standard approach is to use ELSE to avoid unnecessary comparisons and show that only one path can be executed. Something like:
IF @param1 = 1 AND @param2 = 'A'
BEGIN
SELECT ...
...
END
ELSE
IF @param1 = 1 AND @param2 = 'B'
BEGIN
SELECT ...
...
END
ELSE
IF ...
BEGIN
EXEC ...
...
END
....
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 3, 2020 at 7:27 pm
This was removed by the editor as SPAM
November 4, 2020 at 8:32 pm
You have given a vague, general generic description of your code. I will give you a vague general generic solution. The if-then-else construct is a flow control and we don't like to ever use it in a declarative language like SQL. It's a pretty good sign that your code stinks. A good SQL programmer would probably have used CASE expressions instead.
Another design flaw would be the overly general "automobiles, squids, and Lady Gaga" Procedure. A well-designed procedure, regardless of the language, should follow the basic rules of software engineering. A procedure should have one entry point, one exit point and perform one and only one function. Have you ever had a course in basic software engineering? Please look up the terms "coupling", and "cohesion" as design principles.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 5, 2020 at 11:58 pm
Harsh but just. Just like Comrade Stalin.
Joe, when was the last time you walked in a basic software engineering course? Are you sure they still exist? Are you sure “software engineering” is still a thing? It’s all Agile now, you know.
especially in India.
Memorise the frameworks and bake what you’ve told. The faster the better. That’s how it works nowadays.
“Engineering”. Hah. Nice one!
_____________
Code for TallyGenerator
November 6, 2020 at 1:32 pm
Thank you all for your responses.
So please correct that the sys.dm_exec_procedure_stats is not catering such weird stored procedures and dont have such plans as well!!!
cheers
Plans reflect the actually executed statements. Which were picked according to the logic defined by parameters or any other kind of states as they were at the time of execution. It might have nothing to do with the next execution of that procedure.
That's why I suggested to put all the flow control logic into one procedure which calls other procedures holding all the actual data manipulation statements. Then exec_stats will have more relevance to actual SQL statements.
It might also help you to add some structure to the code.
_____________
Code for TallyGenerator
November 9, 2020 at 10:10 am
Plans reflect the actually executed statements. Which were picked according to the logic defined by parameters or any other kind of states as they were at the time of execution. It might have nothing to do with the next execution of that procedure.
That's why I suggested to put all the flow control logic into one procedure which calls other procedures holding all the actual data manipulation statements. Then exec_stats will have more relevance to actual SQL statements.
It might also help you to add some structure to the code.
Great! yes, that it was the supposed question!!! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply