Does the size of a stored procedure affect its execution performance?

Question

Does the size of a stored procedure affect its execution performance?

Is it better to have a large SP that does all the process or to split it to multiple SPs, regarding to performance?

Answer 1

No, not really - or not much, anyway. The Stored Proc is precompiled on the server - and it's not being sent back and forth between server and client - so it's size is really not all that relevant.

It's more important to have it set up in a maintainable and easy to read way.

Marc

Answer 2

Not sure what you mean by the SIZE of a stored procedure ( lines of code?, complexity? number of tables? number of joins? ) but the execution performance depends entirely upon the execution plan of the defined and compiled SQL within the stored procedure. This can be monitored quite well through SQL Profiler if you are using SQL Server. Performance is most heavy taxed by things like joins and table scans, and a good tool can help you figure out where to place your indexes, or think of better ways to define the SQL. Hope this helps.

Answer 3

Let me paraphrase: "Does the size of my function affect it's execution performance?"

The obvious answer is: No. The function will run as fast as it possibly can on the hardware it happens to run on. (To be clear: A longer function will take longer to execute, of course. But it will not run slower. Therefore, the performance is unafffected.)

The right question is: "Does the way I write my function affect it's execution performance?"

The answer is: Yes, by all means.

If you are in fact asking the second question, you should add a code sample and be more specific.

Answer 4

You could possibly cause worse performance by coding multiple stored procedures, if the execution plans need to be flushed to reclaim local memory and a single procedure would not.

We have hit situations where a flushed stored procedure is needed again and must be recompiled. When querying a view accessing hundreds of partition tables, this can be costly and has caused timeouts in our production. Combining into two from eight solved this problem.

On the other hand, we had one stored procedure that was so complex that breaking it up into multiples allowed the query execution plan to be simpler for the chunks and performed better.

The other answers that are basically "it depends" are dead on. No matter how fast of a DB you have, a bad query an bring it to its knees. And each situation is unique. In most places, coding in a modular and easily understandable way, is better performing and cheaper to maintain. SQL server has to "understand" it to, as it builds the query plans.