存储过程的性能调整 [英] Performance tuning for a stored procedure

问题描述

我有一个很大，很大，7到800行的SP。


我不是专家但是我需要弄清楚每一种可能的方式

我可以提高此SP的性能速度。


在接下来的几周内，我将准备SQL语句

将创建表格，插入样本记录并运行SP。

我希望人们会查看我的SP并给我任何关于如何使用b / b
我可以更好地编写SP的提示。 br />

与此同时，在简要查看SP之后，我的第一个观察结果是：


1-使用SET NOCOUNT ON

2-避免使用UNION语句

3-对每个SELECT语句使用WITH（NOLOCK）

4-避免使用NESTED Select语句

5-使用#temp表

6-避免在SELECT语句中重命名表，例如SELECT * FROM

tblClients C


我认为上述6点是
$ b的有效内容是正确的$ b导致

性能问题？

我会很感激任何评论/帮助


非常感谢

I have an SP that is big, huge, 700-800 lines.

I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.

In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.

In the meantime, after looking at the SP briefly, my first observations are:

1- use SET NOCOUNT ON
2- avoid using UNION statements
3- use WITH (NOLOCK) with each SELECT statement
4- avoid using NESTED Select statements
5- use #temp tables
6- avoid renaming tables in SELECT statements, for example SELECT * FROM
tblClients C

Am i correct to consider the above 6 points as valid things in terms of
causing
performance problems?
I would appreciate any comments/help

Thank you very much

推荐答案

嗨Serge，


2004年9月9日星期四00:13:31 -0400，serge写道：

Hi Serge,

On Thu, 9 Sep 2004 00:13:31 -0400, serge wrote:

我有一个很大，很大，7到800行的SP。

我不是专家，但我需要弄清楚每一种可能的方式我可以提高此SP的性能速度。

在接下来的几周内，我将编写SQL语句来创建表，插入样本记录并运行SP 。
我希望人们会看看我的SP并给我任何关于如何更好地编写SP的提示。

同时，简要地看一下SP之后，我的第一个观察是：

1-使用SET NOCOUNT ON


这不会影响性能，但这是一种很好的做法。许多客户

扼杀了众多（n行受影响）。没有这个设置的SP返回的消息

。
2-避免使用UNION语句


取决于。有时，UNION可以比替代方案更快。我已经看到了

的情况，其中带有复杂WHERE子句的查询被评估了带有表扫描的
被重写为UNION的两个（或更多）查询;这些查询中的每一个都是用索引解决的，而且UNION版本更快地运行了一个

。


你可以获得性能通过使用UNION ALL代替UNION，其中

可能。有时，这可以通过扩展UNION中其中一个查询的WHERE

子句来实现（尽管你必须小心

这个扩展的成本不是不会超过UNION ALL的收益

超过UNION！）。


运行以下示例并检查I / O上的执行计划和统计信息/>
和用于执行的时间：


USE pubs

SELECT au_id，au_lname，au_fname

FROM authors

WHERE au_id喜欢''2％''

或者au_lname =''铃声''

GO

SELECT au_id，au_lname，au_fname

FROM authors

WHERE au_id LIKE''2％''

UNION

SELECT au_id，au_lname，au_fname

FROM authors

WHERE au_lname =''Ringer''

GO

SELECT au_id，au_lname，au_fname

FROM authors

WHERE au_id LIKE''2％''

UNION ALL

SELECT au_id，au_lname，au_fname

FROM authors

WHERE au_lname =''Ringer''

AND au_id NOT LIKE''2％ ''

GO


3-使用WITH（NOLOCK）和每个SELECT语句


这可以让你获益一些性能（特别是如果并发性很高，你的数据库是
），但是你冒着脏读的风险。如果风险

可以接受，请继续。如果你不知道脏读是什么，那么

不要使用这个选项。


4-避免使用NESTED Select语句


再次：依赖。如果您可以安全轻松地使用其他代码替换嵌套选择（或

子查询，因为它通常被称为），请执行此操作，然后测试

版本到看看性能是否真的有所改善（如果经常不会改进，b
，因为SQL Server的优化器已经使用了相同的执行计划）。


类似地，如果您可以安全地将相关子查询替换为非关联的

，请执行此操作并测试两个版本。


但是如果删除子查询意味着你必须编写更多的SQL代码，它可能会损害性能而不是改进它。如果你可以通过用一个人为的和难以理解的查询替换一个直观的子查询来获得一些

的性能，那么你可能想重新考虑一下你是否真的重视

性能高于可维护性。有一天，你会发现自己

盯着那个查询，想知道％

I have an SP that is big, huge, 700-800 lines.

I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.

In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.

In the meantime, after looking at the SP briefly, my first observations are:

1- use SET NOCOUNT ON
This will not affect performance, but it is good practice. Many clients
choke on the numerous "(n row(s) affected)" messages returned by an SP
without this setting.

2- avoid using UNION statements
Depends. Sometimes, a UNION can be quicker than the alternative. I''ve seen
cases where a query with a complicated WHERE clause that was evaluated
with a table scan was rewritten as two (or more) queries with UNION; each
of those queries was resolved with an index and the UNION version ran a
lot quicker.

You can gain performance by using UNION ALL instead of UNION where
possible. Sometimes, this can be made possible by extending the WHERE
clause of one of the queries in the UNION (though you have to be carefull
that the cost of this extension doesn''t outweigh the gain of UNION ALL
over UNION!).

Run the following examples and check execution plans and statistics on I/O
and time used for execution:

USE pubs
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE ''2%''
OR au_lname = ''Ringer''
GO
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE ''2%''
UNION
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = ''Ringer''
GO
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_id LIKE ''2%''
UNION ALL
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = ''Ringer''
AND au_id NOT LIKE ''2%''
GO

3- use WITH (NOLOCK) with each SELECT statement
This can gain you some performance (especially if concurrency is high on
your database), but you run the risk of getting dirty reads. If that risk
is acceptable, go ahead. If you don''t know what a dirty read is, then
don''t use this option.

4- avoid using NESTED Select statements
Again: depends. If you can safely and easily replace the nested select (or
subquery, as it''s usually called) with other code, do so, then test both
versions to see if performance actually has improved (if often won''t
improve, as SQL Server''s optimizer already uses the same execution plan).

Similar, if you can safely replace a correlated subquery with a
non-correlated, do so and test both versions.

But if removing the subquery means that you have to code lots more SQL, it
might hurt performance instead of improving it. And if you can gain some
performance by replacing an intuitive subquery with a contrived and hard
to understand query, then you might want to reconsider if you really value
performance higher than maintainability. One day, you will find youself
staring at that query, wondering what the %

＃that ^％＃

&％＃查询是

应该这样做。


5-使用#temp表


At重复自己的风险：取决于。如果你发现在程序中反复使用相同的子查询

，那么如果你将
执行该子查询到#temp表并将其用于其余部分，它可能会帮助你提高性能

执行。它可能还有助于进一步索引临时表。但是，再次，

它可能也会损害性能 - 创建临时表并存储

数据也会产生一些开销，如果你不小心，你可能会是
面对存储过程的大量重新编译，如果没有临时表，则不需要
。


如果你使用临时表来逐步打破复杂的查询，你b / b
很有可能降低性能。在一个复杂的查询中，

优化器可能会选择一个你永远不会想到的执行计划，但是它比执行它的显而易见的方式更快;如果你通过临时表执行中间结果来决定步骤

，那么你需要从优化器中获取很多选项。当然，还有对代码的可维护性和可读性的考虑因素，所以你可以选择接受性能下降，只是为了你将

了解你的代码（不是!!），你（或其他人）必须在以后再拿回

。


6 - 避免在SELECT语句中重命名表，例如SELECT * FROM
tblClients C


我从未听说使用表别名（因为这称为）会伤害

表现。如果您有任何证据，请指出。我b / b
会非常惊讶。


事实上，当你使用同一张桌子时，绝对需要使用别名

more在查询中和使用派生表时的一次;在所有其他

的情况下（除了单表查询或非常短的表名）我还要

衷心建议使用别名。选择一个助记符别名，而不仅仅是一个随机的一两个字母组合！


我认为以上6点作为有效的东西是正确的
造成性能问题？

&%# query is
supposed to do.

5- use #temp tables
At the risk of repeating myself: depends. If you find the same subquery
used over and over in the procedure, it MIGHT help performance if you
execute that subquery into a #temp table and use that for the rest of the
execution. It MIGHT also help further to index the temp table. But, again,
it might also hurt performance - creating the temp table and storing the
data induces some overhead as well and if you''re not careful, you might be
faced with numerous recompilationms of the stored procedure that wouldn''t
be needed without the temp table.

If you use a temp table to break a complicated query down in steps, you
have a good chance of degrading performance. In one complicated query, the
optimizer may choose an execution plan that you would never think of but
that''s faster than the obvious way to execute it; if you dictate the steps
by executing them seperateely with a temp table for intermediate results,
you take a lot of options from the optimizer. Of course, there is also the
consideration of maintainability and readability of your code, so you
might choose to accept the performance degradation, just so that you will
understand your code when (not if!!) you (or someone else) have to get
back to it later.

6- avoid renaming tables in SELECT statements, for example SELECT * FROM
tblClients C
I''ve never heard that using a table alias (as this is called) would hurt
performance. If you have any evidence of this, please point me to it. I
would be highly surprised.

In fact, using an alias is absolutely needed when you use the same table
more than once in a query and when you use derived table; in all other
cases (except for single-table queries or very short table names) I''d also
heartily recommend using an alias. Do choose a mnemonic alias, not just a
random one or two letter combination!

Am i correct to consider the above 6 points as valid things in terms of
causing
performance problems?

见上文。您可能还想看一下这个网站：
http： //www.sql-server-performance.com/

Best，Hugo

-


（删除_NO_和_SPAM_以获取我的电子邮件地址）

See above. And you might also want to take a look at this site:
http://www.sql-server-performance.com/
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

这篇关于存储过程的性能调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！