Regarding Performance - Running Multiple Procs

Question

Regarding Performance - Running Multiple Procs

Vinu verma

Right there with Babe

Points: 795
More actions
December 5, 2007 at 2:11 pm

#377356

Hi Guys,
Not sure whether this the correct forum to ask this, but anyway i wanted to throw out it to you guys and get the expert advice.
We have an existing app which is used for generated and its pretty slow. This is how the order of sequence
1) 5 Procs run to create a schema XSD
2) This schema has 5 nodes which represents the datasource for 5 reports( 1 main and 4 sub reports)
I can modify to have all the 5 Procs to be created as one which will be employ the use of proper indexes.
So which one will be more efficient, running it as 5 different procs or running it as one single proc.
Please advice
Thanks
VinuVerma

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

If the 5 procs can be executed at the same time and you have multiple processors, I think the 5 procs running in parallel with win out over the single serial proc... provided you don't have dependencies between the procs.

--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)

EdVassie SSC Guru Points: 60445 More actions · Answer 2

It is very unlikely that merging the procs will improve performance.

You should analyse what each statement is doing in the procs, to see where the delays are happening.

As a wild guess, are you creating any new database files in the procs? If so, make sure the SQL Server service account has the Windows 'perform volume maintenance tasks' right, which will enable instant file initialisation in SQL.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara