English 中文(简体)
性能调整 SQL - 如何实现?
原标题:
  • 时间:2009-01-21 00:08:52
  •  标签:

如何对 SQL 查询进行性能调优?

  • What tricks/tools/concepts can be used to change the performance of a SQL Query?
  • How can the benefits be Quantified?
  • What does one need to be careful of?


What tricks/tools/concepts can be used to change the performance of a SQL Query?

  • Using Indexes? How do they work in practice?
  • Normalised vs Denormalised Data? What are the performance vs design/maintenance trade offs?
  • Pre-processed intermediate tables? Created with triggers or batch jobs?
  • Restructure the query to use Temp Tables, Sub Queries, etc?
  • Separate complex queries into multiples and UNION the results?
  • Anything else?


How can performance be Quantified?

  • Reads?
  • CPU Time?
  • "% Query Cost" when different versions run together?
  • Anything else?


What does one need to be careful of?

  • Time to generate Execution Plans? (Stored Procs vs Inline Queries)
  • Stored Procs being forced to recompile
  • Testing on small data sets (Do the queries scale linearly, or square law, etc?)
  • Results of previous runs being cached
  • Optimising "normal case", but harming "worst case"
  • What is "Parameter Sniffing"?
  • Anything else?


Note to moderators: This is a huge question, should I have split it up in to multiple questions?

Note To Responders: Because this is a huge question please reference other questions/answers/articles rather than writing lengthy explanations.

最佳回答

我非常喜欢这本书《Professional SQL Server 2005性能调优》,它是Wiley/Wrox出版的,不过我不是作者。它解释了你在这里问的很多问题,还涉及硬件问题。

但是,是的,这个问题远远超出了可以在评论框中回答的范围。

问题回答

编写可搜索的查询是必需的一件事情,如果您不写可搜索的查询,则优化器无法利用索引。这里举一个例子:只有在数据库中,你才能通过改动几行代码获得1000% + 的改善,这个查询从超过24小时到只需36秒。

以下是一些需要遵循的基本步骤:

  1. Define business requirements first
  2. SELECT fields instead of using SELECT *
  3. Avoid SELECT DISTINCT
  4. Create joins with INNER JOIN (not WHERE)
  5. Use WHERE instead of HAVING to define filters
  6. Proper indexing

当然,您还需要知道这3个链接之间的区别。

loop join, hash join, merge join

看这里:http://msdn.microsoft.com/en-us/library/ms173815.aspx

这里是一些基本步骤,我们可以遵循以提高性能:

  1. Check for indexes in pk and fk for the tables involved if it is still taking time index the columns present in the query.
  2. All indexes are modified after every operation so kindly do not index each and every column
  3. Before batch insertion delete the indexes and then recreate the indexes.
  4. Select sparingly
  5. Use if exists instead of count
  6. Before accusing dba first check network connections




相关问题
热门标签