优化查询 [英] Optimizing the Query

问题描述

你好朋友..

请任何人告诉我，如何减少后端过程的执行时间.

它需要很多时间来显示结果.当我使用游标..
但不确定如何优化程序..


问候，
Priya

Hello Friends..

Please can any one tell me that how can i decrease the Execution Time for the BackEnd Procedure.

Its taking alot of time to display the result. As i used Cursors..
But not sure how to optimize the procedure..


Regards,
Priya

推荐答案

要优化查询，您应该注意以下事项:

Hi,
To optimize your query, you should take care about the following:

报价:

表应具有主键
1.表应至少具有一个聚集索引
2.表中应有适当数量的非聚集索引
3.应基于正在运行的查询在表的列上创建非聚集索引
4.创建任何索引时，应遵循以下优先顺序:a)WHERE子句，b)JOIN子句，c)ORDER BY子句，d)SELECT子句
5.请勿使用视图或将视图替换为原始源表
6.尽可能不使用触发器，将触发器的逻辑合并到存储过程中
7.删除所有临时查询，并改用存储过程
8.检查是否有至少30％的HHD为空–这会稍微提高性能
9.如果可能，也将UDF的逻辑也移到SP
10.从SELECT中删除*，并使用仅在代码中必需的列
从表中删除任何不必要的联接
11.如果查询中使用了游标，请查看是否还有其他方法可以避免使用此游标(通过SELECT…INTO或INSERT…INTO等)

Table should have primary key
1. Table should have minimum of one clustered index
2. Table should have appropriate amount of non-clustered index
3. Non-clustered index should be created on columns of table based on query which is running
4. Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
5. Do not to use Views or replace views with original source table
6. Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
7. Remove any adhoc queries and use Stored Procedure instead
8. Check if there is atleast 30% HHD is empty – it improves the performance a bit
9. If possible move the logic of UDF to SP as well
10. Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
11. If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

欲了解更多信息，请点击以下链接:
http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179 [ http://msdn.microsoft.com/en-us/library/aa964133％28v = sql.90％29.aspx [ ^ ]


--Amit

For more info follow the links below:
http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[^]
http://msdn.microsoft.com/en-us/library/aa964133%28v=sql.90%29.aspx[^]


--Amit

使用查询分析器& SQL Server的 SQL Profiler 来查找查询成本和性能.查看瓶颈在哪里，并尝试对其进行微调.

参考文献:
查询分析器，位于以下位置:
-程序> Microsoft SQL Server 2008 R2>用于查询分析器的SQL Server Management Studio.
-程序> Microsoft SQL Server 2008 R2>性能工具>用于Profiler的SQL Server Profiler.

DB中的视图:
MSDN:创建VIEW [ SQL Server 2005中的视图概述 [ ^ ]


您使用的游标不是一种好的做法，请改用TableValue函数，然后查看.

Use Query analyzer & SQL Profiler of SQL Server to find the query cost and performance. See where the bottle neck is and try to fine tune it.

References:
Query Analyzer, find at location:
- Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio for Query Analyzer.
- Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler for profiler.

Views in DB:
MSDN: Create VIEW[^]
Overview of Views in SQL Server 2005[^]


You are using cursors which is not considered good practice, try TableValue functions instead and see.

这篇关于优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！