任何想要优化这种类型的查询的想法 [英] Any ideas to optimize this type of queris

问题描述

在以下查询中，多次使用表1 以获取准确的摘要数据。但是，当表1 中的总记录增加到400K时，查询执行时间超过15秒。



邀请您的建议/查看点以提高此查询的性能。

Hi,

In the following query, Table 1 is used many times in order to get accurate Summary data. However when the total records in Table 1 increased to 400K, the query is taking more than 15 seconds to execute.

Inviting your suggestion / view points to improve performance of this query.

Select 
(Select Count(Distinct [Table1].[BusinessUnit]) From [Table1] (nolock)) As [No. of Buss Unit],  
(Select Count(Distinct [Table1].[Segment]) From [Table1] (nolock)) As [No. of Segments],  
(Select Count(Distinct [Table1].[Vendor]) From [Table1] (nolock)) As [No. of Vendors],  
(Select Count(Distinct [Table1].[Payment]) From [Table1] (nolock)) As [No. of Payments],  
(Select Count(Distinct [Table1].[Payee]) From [Table1] (nolock)) As [No. of Payees],  
(Select Count(Distinct [Table1].[Country]) From [Table1] (nolock)) As [No. of Countries],  
Avg([Table1].[Amount]) As [Avg Payment Amount],  
Min([Table1].[Amount]) As [Min Payment Amount],  
Max([Table1].[Amount]) As [Max Payment Amount],  
Sum([Table1].[Amount]) As [Total Payment Amount],  
Min([Table1].[Date]) As [Min Date],  
Max([Table1].[Date]) As [Max Date],  
Min([Table1].[CPI]) As [Minimum CPI Score],  
Max([Table1].[CPI]) As [Maximum CPI Score] 

From [Table1] (nolock)

推荐答案

从不轻松：它需要一些工作。

这个： http://www.anchor.com.au/hosting/dedicated/SQL_Query_Optimisation [ ^ ]提供了良好的背景，但它不是MsSql相关的（它是MySql / PostgreSQL） - 进行分析的方法不同但技术是相同的。



Google也会提供帮助： optimize + sql + query + performance [ ^ ] - 但实际的工作取决于你，因为你是唯一一个可以访问DB和SQL实例的人！

Never easy: it's going to take some work.
This: http://www.anchor.com.au/hosting/dedicated/SQL_Query_Optimisation[^] provides good background, but it's not MsSql related (it's MySql/PostgreSQL) - the methods to do the analysis are different but the techniques are the same.

Google will also help: optimise+sql+query+performance[^] - but the actual work is going to be up to you, since you are the only one with access to the DB and SQL instance!

显而易见的解决方案通常是使用分析函数将表读取的数量限制为只有一个。

但SQLServer不支持 Count（Distinct foo）结束（Foobar按条排序）



幸运的是有一种解决方法：

The obvious solution would normally be the use of analytical functions to limit the number of table reads to just one.
But SQLServer does not support Count(Distinct foo) over (Partition By Bar Order by Foobar)

Luckily there's a workaround:

SELECT  
        DENSE_RANK() over (order by [BusinessUnit] ASC) + DENSE_RANK() over (order by [BusinessUnit] DESC) - 1 As [No. of Buss Unit],  
        DENSE_RANK() over (order by [Segment] ASC) + DENSE_RANK() over (order by [Segment] DESC) - 1 As [No. of Segments],  
        DENSE_RANK() over (order by [Vendor] ASC) + DENSE_RANK() over (order by [Vendor] DESC) - 1 As [No. of Vendors],  
        DENSE_RANK() over (order by [Payment] ASC) + DENSE_RANK() over (order by [Payment] DESC) - 1 As [No. of Payments],  
        DENSE_RANK() over (order by [Payee] ASC) + DENSE_RANK() over (order by [Payee] DESC) - 1 As [No. of Payees],  
        DENSE_RANK() over (order by [Country] ASC) + DENSE_RANK() over (order by [Country] DESC) - 1 As [No. of Countries],  
        Avg([Amount]) As [Avg Payment Amount],
        Min([Amount]) As [Min Payment Amount],  
        Max([Amount]) As [Max Payment Amount],  
        Sum([Amount]) As [Total Payment Amount],  
        Min([Date]) As [Min Date],  
        Max([Date]) As [Max Date],  
        Min([CPI]) As [Minimum CPI Score],  
        Max([CPI]) As [Maximum CPI Score]
FROM    [Table1]

您好


如果您使用的是同一张表为什么要在每个组中使用from子句。

一次只使用from子句并在单个查询中应用多个组函数。





谢谢

Mangesh

Hi
If you are using same table then why should you used "from" clause every group.
Use from clause only at once and apply multiple group function in single query.


Thanks
Mangesh

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