May 31, 2018 at 8:50 am
Hi,
I have ONE report and it has 5 sections. I wrote a stored procedure and each report section takes different parameters using same procedure.
Is it good to have 5 different stored procedures calling one time when the report is executed VS 1 stored proc being called five times with different set of parameters.
I believe tables are hit 5 times in both cases. One advantage is that of re-compile is not needed if only 1 proc is used and less db objects maintenance too.
I wanted to understand any other advantages or which option is better?
Thanks,
Naveen.
Every thought is a cause and every condition an effect
May 31, 2018 at 8:55 am
Naveen PK - Thursday, May 31, 2018 8:50 AMHi,
I have ONE report and it has 5 sections. I wrote a stored procedure and each report section takes different parameters using same procedure.
Is it good to have 5 different stored procedures calling one time when the report is executed VS 1 stored proc being called five times with different set of parameters.
I believe tables are hit 5 times in both cases. One advantage is that of re-compile is not needed if only 1 proc is used and less db objects maintenance too.
I wanted to understand any other advantages or which option is better?
You might get a better execution plan when having 5 procedures as the compile will get the adequate parameters for each part.
May 31, 2018 at 1:17 pm
Naveen PK - Thursday, May 31, 2018 8:50 AMHi,
I have ONE report and it has 5 sections. I wrote a stored procedure and each report section takes different parameters using same procedure.
Is it good to have 5 different stored procedures calling one time when the report is executed VS 1 stored proc being called five times with different set of parameters.
I believe tables are hit 5 times in both cases. One advantage is that of re-compile is not needed if only 1 proc is used and less db objects maintenance too.
I wanted to understand any other advantages or which option is better?
Hard to know without detailed knowledge of the report itself. One would need to know those details pretty well to have any idea which would work better. Luis is correct, but again, without rather specific details, it's all guesswork. That also explains why he said "might"...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 1, 2018 at 1:49 pm
You want five separate stored procs regardless. Then, if you want only to have to call 1 main proc from the report/SSRS/whatever, create a parent/"driver" proc that executes those 5 procs.
Then if, for example. some process/app needed only 3 of the 5 reports, you'd just exec those 3 procs, without all the extreme headaches of trying to break the combined proc up and/or skip parts of it (yuck!).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply