通过向数据库添加索引来提高应用程序性能 [英] Improve an application performance by adding indexes to a database
问题描述
我有一个带有大量遗留代码的旧 Web 应用程序，我需要提高某些报告的性能.我想避免重写报告，因为它是标准的遗留应用程序，难以维护、测试和开发.所以我试图通过在 MSSQL (2008) 中为报告查询添加索引来加速这些报告.报告执行大约 3-4k 查询.
I've got an old web application with a lot of legacy code and I need to improve performance for some reports. I want to avoid reports rewrite, because it's standard legacy application which is hard to maintain, test and develop. So I'm trying to speed these reports by adding indexes for reports queries in MSSQL (2008). Reports execute about 3-4k queries.
以下是我的改进计划:
1. 通过运行
Below is my improvement plan:
1. Clean MSSQL buffers by running
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
2.运行 MSSQL 探查器，然后执行报告.
3. 收集所有报表的查询，然后将它们导出到 MSSQL.
4. 计算持续时间的总和.
5. 按textdata 对查询进行分组，并按sum(duration) desc 排序.
6. 从分析器的导出中执行查询并添加 MSSQL Management Studio 推荐的索引.
7. 重复步骤 1 到 6 并比较结果.
2. Run MSSQL profiler and then execute report.
3. Collect all report's queries and then export them to MSSQL.
4. Calculate sum of duration.
5. Group queries by textdata and order them by sum(duration) desc.
6. Execute queries from profiler's export and add indexes which are recommended by MSSQL Management Studio.
7. Repeat steps from 1 to 6 and compare results.
我得到了有趣的结果.当我提高报告 #1 的性能时，报告 #2 的性能会降低.有时在添加索引后，我的性能下降了十倍.
I've got interesting results. When I improve report #1 performance, report #2 performance are decreased. Also sometimes after adding indexes I've got performance decreasing tenfold.
我的问题:
1. 通过提高 SQL 查询性能来提高应用程序性能是个好主意吗?
2. 是否可以通过添加索引来提高执行 3-4k SQL 查询的报表的性能?
My questions:
1. Is it a good idea to improve application performance by improving SQL queries performance?
2. Is it possible to improve performace of reports which execute 3-4k SQL queries by adding indexes?
推荐答案
对于性能不佳的数据库，不适当/缺失的索引可能是众多原因之一.
For a poor performing database, inappropriate/missing indexes can be ONE of the many reasons.
如果你认为没有足够的索引是导致穷人的原因性能然后有工具来检查丢失的索引是否是实际问题，但有时问题可能是查询本身.
If you think not having enough indexes is what causing the poor performance then there are tool to check if the missing indexes is the actual problem, but sometimes problem can be the query itself.
如果你的查询写得不好，无论你添加多少索引会表现不佳.
If your query is written poorly no matter how many indexes you add it will perform poor.
有时查询写得很好并且有足够的索引来支持查询但没有足够的资源(内存 RAM、处理器马力)以快速完成工作.
Sometimes query is written fine and there are enough indexes to support the query but there isn’t enough resources (Memory RAM, processor horse power) to get the job done quickly.
我建议您先调查一下您的数据库开始添加索引并引入更多从未存在的问题之前.
I would suggest you to investigate your database a bit more before you start adding indexes and introduce more problems which never existed before.
一些有用的链接
这将允许您查看是否有任何缺失的索引，并且查询可以通过添加索引来提高性能.
This will allow you to see if there are any missing indexes and queries may gain performance by adding indexes.
这篇由 Paul Randal 撰写的博客将帮助您准确了解导致 sql 服务器性能下降的原因.无论是您的硬件、索引还是查询.
This blog written by Paul Randal will help you to see exactly what is causing slow performance in your sql server. Whether it be your hardware or indexes or queries.
这将允许您查看数据库中最昂贵的查询，我会逐个查询并尝试改进代码(如果可能)并检查是否有任何索引可以帮助查询降低其成本.
This will allow you to see the most expensive queries in your database, I would go query by query and try to improve the code (if possible) and also check if any index can help the query to reduce its cost.
导入注释
在 sql server 中进行性能调优不是一项简单的任务，也没有灵丹妙药，您需要进行更多调查以找到数据库中的实际瓶颈并针对它们进行定位，并在每次更改时进行比较更改已影响您的数据库性能.祝你好运:)
Performance tunning in sql server is no simple task and there is no silver bullet for it either, you will need to investigate more to find the actual bottlenecks in your database and target them, and with each change do a comparison that how that change has impacted your database performance. Good luck with that :)
这篇关于通过向数据库添加索引来提高应用程序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
