SQL Server 2005 - 优化存储过程 [英] SQL Server 2005 - optimizing stored procedures

问题描述

我在 SQL Server 2005 中有 2 个存储过程(sp1 和 sp2)，使用相同的 sql 脚本(除了名称之外没有任何区别).

I have 2 stored procedures (sp1 and sp2) in SQL Server 2005 with the same sql script (no diff at all but the name).

当我尝试执行 sp1 时，它会在 0-1 秒内返回超过 3000 行的结果.但是当我运行 sp2 时，它会在 10 秒内返回那 3000 行.

When I try to execute sp1 it returns more than 3000 rows as result, in 0-1 secs. But when I run sp2 it will return those 3000 rows in 10 seconds.

想知道有没有什么工具/命令/东西可以设置特定的存储过程来优化好?

Would like to know is there any tool/command/anything which can set a specific stored procedure to optimize well?

谢谢.

推荐答案

SQL Server 保存如何执行代码的计划".

SQL Server saves "plans" of how to execute code.

如果存储过程针对一小部分数据运行，它将针对小数据集进行优化.对于大型数据集，情况正好相反.

If a stored procedure is run against a small subset of data it will optimize for a small dataset. The opposite is true for a large dataset.

在 2008 年有一个很好的 OPTIMIZE FOR 功能，但在 2005 年你被 WITH RECOMPILE 困住了.这意味着每次运行时都会重新编译，这在某些情况下是最优化的！

Theres a nice OPTIMIZE FOR feature in 2008 but in 2005 you are stuck with WITH RECOMPILE. This means it will be recompiled each time it is run, which, in some cases is most optimal!

这篇关于SQL Server 2005 - 优化存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！