需要调整查询以提高性能 [英] Need to tune quries to improve performance

问题描述

我有3张桌子a，b，c.

Hi,

I have 3 table a,b,c.

select * from a

 select * from b where aid in (select id from a)

 select * from c where bid in (select Id from b where aid in (select id from a))

这些在sp中的查询并给出正确的结果，但是作为性能，我需要优化这些
你能建议我如何提高性能.是可用于优化的任何工具吗?
sql查询

感谢

these quries in sp and giving correct result but as performance I need to optimise these
could you please suggest how do i improve perfomance. is any tools available to optimised
sql queries

Thanks

推荐答案

规则一:将"*"替换为您实际想要的字段的列表.根据您要检索的数据，这可能对带宽/内存占用量产生巨大的影响.

Rule one: replace "*" with a list of the field you actually want. Depending on the data you are retrieving, this can make a massive difference to the bandwidth / memory footprint.

检查这一堆

在SQL Server中优化数据访问的十大步骤:第一部分(使用索引编制) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第二部分(重构TSQL并应用最佳实践) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第三部分(应用高级索引和非规范化) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第四部分(诊断数据库性能问题) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第五部分(优化数据库文件并应用分区) [ ^ ]

Check this bunch

Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

将查询嵌入到StoredProcedures中是非常好的一步，因为SQL Server会制定一些执行计划来运行它们.

您的查询似乎处于最优化状态(在此处阅读 in-vs-join- vs-exists/ [ ^ ])，因此请尝试在应用程序端进行操作.如果您要处理数百万条记录，请不要尝试将所有记录都显示给用户，请尝试按字母顺序或您更了解的其他一些分组条件对它们进行分组.

添加where子句并限制结果集可以提高应用程序性能.

还可以考虑使用一些支持延迟加载的UI控件.

希望对您有帮助.

That you have embedded your queries into StoredProcedures is a very good step because SQL Server will make some execution plans to run them.

Your queries seem to be at their most optimized state (read here in-vs-join-vs-exists/[^]) so try to work on application side. If you are working with millions of record do not try to show all of them to user try to group them in alphabetic manner or some other grouping criteria that you know better.

Adding a where clause and confining the result set should improve your application performance.

Also consider using some UI controls that support lazy loading.

Hope It Helps.

这篇关于需要调整查询以提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！