如何在服务器2012中优化SQL查询（ssms） [英] how to optimize a SQL query inside server 2012(ssms)

问题描述

嗨



我有一个查询，这只是4个表中的select语句和3个连接的视图......而且它需要花费很多时间.. ..所以我需要优化它，并希望执行计划.....临时表工作.....这里我在做什么



选择o。 aa，b.cc，c.dd

来自aaa a

内部联接bbb b（条件）

内部联接ccc c（条件）

离开加入ddd d（条件）

where（这里有3个嵌套的选择语句）



可以请你帮助我如何优化它，或者我如何使用临时表来修复它

ddd不给上面的字段，它只给出上述条件的id





谢谢

解决方案

如评论中所述，您可以使用多种工具来帮助您找出问题所在。



例如，使用查询菜单下的显示估计执行计划选项。如果您在SQL方面不够强大，那么这些计划很难阅读，但您通常会寻找显示大量处理百分比的1或2步。



然后您还可以使用数据库引擎优化顾问中的分析查询，也可以在查询菜单下，它将打开一个新窗口并运行您的查询，然后建议索引和其他项目以提高性能。



另外，正如PIEBALDconsult所提到的，在WHERE子句中有3个SELECT语句似乎可能是个问题，除非它们是非常简单的select语句。但是，它们可能会被转换为连接。



由于我们看不到您的任何表或数据，我们无法提供任何非常具体的信息。

Hi

I have a query , which is only select statement from 4 tables and views with 3 joins ... and its taking a lot of time .... so i need to optimize it and want execution plan ..... will temp table works ..... here what i am doing

select o.aa ,b.cc,c.dd
from aaa a
inner join bbb b (condition)
inner join ccc c (condition)
left join ddd d (condition )
where ( here 3 nested select statements )

can u please help how can i optimize it , or how can i use temp tables to fix it
ddd is not giving fields above , its only giving id for above conditions


Thanks

解决方案

As mentioned in comments, you can use several tools to help you find out what the problem is.

For example, use the "Display Estimated Execution Plan" option under the Query menu. These plans are hard to read if you are not strong in SQL but you'll be looking for usually 1 or 2 steps that show a lot of processing percentage.

Then you can also use the Analyze Query in Database Engine Tuning Advisor, also under the Query menu, which will open a new window and will run your query and then will suggest indexes and other items to improve performance.

Also, as PIEBALDconsult mentioned, having 3 SELECT statements in the WHERE clause seems like may be the issue, unless they are very simple select statements. However, they might be able to be converted into joins instead.

Since we can't see any of your tables or data we can't give anything very specific.

这篇关于如何在服务器2012中优化SQL查询（ssms）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！