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?
对于性能而言,拥有执行所有过程的大型SP或将其拆分为多个SP更好吗?
4 个解决方案
#1
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.
如果您实际上是在问第二个问题,那么您应该添加一个代码示例并且更具体。
#2
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
#3
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.
不确定存储过程的SIZE是什么意思(代码行?,复杂性?表的数量?连接数?)但执行性能完全取决于存储过程中定义和编译的SQL的执行计划。如果您使用的是SQL Server,可以通过SQL事件探查器很好地监视这一点。性能最重要的是连接和表扫描等问题,一个好的工具可以帮助您找出索引的放置位置,或者考虑更好的方法来定义SQL。希望这可以帮助。
#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.
基本上“依赖于”的其他答案已经死亡。无论你有多快的数据库,一个糟糕的查询都会让它瘫痪。每种情况都是独一无二的。在大多数地方,以模块化且易于理解的方式进行编码,性能更好,维护成本更低。 SQL Server必须“理解”它,因为它构建了查询计划。
#1
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.
如果您实际上是在问第二个问题,那么您应该添加一个代码示例并且更具体。
#2
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
#3
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.
不确定存储过程的SIZE是什么意思(代码行?,复杂性?表的数量?连接数?)但执行性能完全取决于存储过程中定义和编译的SQL的执行计划。如果您使用的是SQL Server,可以通过SQL事件探查器很好地监视这一点。性能最重要的是连接和表扫描等问题,一个好的工具可以帮助您找出索引的放置位置,或者考虑更好的方法来定义SQL。希望这可以帮助。
#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.
基本上“依赖于”的其他答案已经死亡。无论你有多快的数据库,一个糟糕的查询都会让它瘫痪。每种情况都是独一无二的。在大多数地方,以模块化且易于理解的方式进行编码,性能更好,维护成本更低。 SQL Server必须“理解”它,因为它构建了查询计划。