如何提高10分钟查询的性能？ [英] How to improve the performance of a 10 min query?

问题描述

我有一个查询，大约需要10分钟来执行并生成结果。当我试图将其分解为部分并运行它时，它似乎在几秒钟内运行良好。



我试图修改查询顶部的子部分并确定是否导致问题，但事实并非如此。它在3秒内给出了一些结果。



然后我分别运行底部部分，

I have a query which is taking approximately 10 mins to execute and produce the results. When I try to break it into parts and run it, it seems to run fine, within seconds.

I tried to modify the subselect of the top portion of the query and determine if that was causing the issue, but it was not. It gave out some results within 3 seconds.

Then I ran the bottom portion separately,

join(select glP.fkosControlNumberStatus....

它在1秒内运行。



我正在努力学习阅读估计执行计划，但我越来越难以追查到这个问题。



有人可以帮助我提高这个查询的性能吗？



我尝试过：

And it ran within 1 second.

I am trying to learn to read the Estimated Execution plan, but it is becoming more confusing and hard for me to trace to the issue.

Can anyone please assist me in improving the performance of this query please?

What I have tried:

Set Nocount On
Declare @YearEndDate DateTime = '2016-12-31'
set @YearEndDate = dbo.fnGetDatePartOnly(@YearEndDate)

Select Distinct
       glPostExtended.BatchNumber,
       postData.fkosControlNumberStatus,
       postData.AccountSegment Fund,
       postdata.Debit,
       postData.Credit,
       postdata.DistAmountTotal,
       glPostExtended.SubLedger Module,
       glPost.SourceJENumber,
       glPost.PostToDate
From
       glPost WITH (NOLOCK)
       --This joins limits the glPostExtended Records from duplicating glPost Records for Summarized posting
    join (Select fkglPost, MAX(fkglDist) as fkglDist, COUNT(fkglDist) as RecordCount From glPostExtended WITH (NOLOCK) Group By fkglPost) as glPostExtendedLimiter on glPost.PK = glPostExtendedLimiter.fkglPost
    join glPostExtended WITH (NOLOCK) on glPostExtendedLimiter.fkglPost = glPostExtended.fkglPost and (glPostExtendedLimiter.fkglDist = glPostExtended.fkglDist or glPostExtended.fkglDist is null)
       join (select glP.fkosControlNumberStatus, glP.SourceJENumber, AccountSegment, 
                 sum(case 
                       when glP.DistAmount > 0 then glP.DistAmount
                       else 0
                 end) Debit,
                 sum(case
                       when glP.DistAmount < 0 then glP.DistAmount
                       else 0
                 end) Credit,
                 sum(glP.DistAmount) DistAmountTotal
                from glPost glP WITH (NOLOCK)
                  join glAccountingPeriod WITH (NOLOCK) on glAccountingPeriod.pk = glP.fkglAccountingPeriod
                  join glFiscalYear WITH (NOLOCK) on glFiscalYear.pk = glAccountingPeriod.fkglFiscalYear
                  join glAccount WITH (NOLOCK) on glAccount.pk = glFiscalYear.fkglAccount
                where dbo.fnGetDatePartOnly(glFiscalYear.PeriodEnd) = @YearEndDate
                 and glP.fkMLSosCodeEntryType = 2202
                group by glP.fkosControlNumberStatus, glP.SourceJENumber, AccountSegment) postdata on postdata.fkosControlNumberStatus = glPost.fkosControlNumberStatus and postdata.SourceJENumber = glPost.SourceJENumber
where postdata.DistAmountTotal <> 0
  and glPost.fkMLSosCodeEntryType = 2202
order by glPostExtended.BatchNumber, glPost.SourceJENumber, postData.AccountSegment

尝试修改这样的顶级子选择连接部分并且不需要太长时间

Tried modifying the top subselect join part like this and it didn't take too long

Select Distinct
	glPostExtended.BatchNumber,
	--postData.fkosControlNumberStatus,
	--postData.AccountSegment Fund,
	--postdata.Debit,
	--postData.Credit,
	--postdata.DistAmountTotal,
	glPostExtended.SubLedger Module,
	glPost.SourceJENumber,
	glPost.PostToDate
From
	(Select fkglPost, MAX(fkglDist) as fkglDist, COUNT(fkglDist) as RecordCount From glPostExtended WITH (NOLOCK) Group By fkglPost) as glPostExtendedLimiter
	join glPostExtended WITH (NOLOCK) on glPostExtendedLimiter.fkglPost = glPostExtended.fkglPost and (glPostExtendedLimiter.fkglDist = glPostExtended.fkglDist or glPostExtended.fkglDist is null)
	 inner join glPost WITH (NOLOCK) 
		on glPostExtendedLimiter.fkglPost = glPost.PK 

推荐答案

这不是我们真正能够做到的事情没有相似的表格数据可以帮助我们实现。



在理解执行计划方面，您可能会发现此CodeProject文章很有用： SQL Tuning Tutorial - 了解数据库执行计划（1） [ ^ ]



你似乎有很多子查询，你可能想要尝试合理化。我会再看一下，看看还有什么可以尝试的。



[更新]我无法证明，但我很确定如果你采取一些子查询并将数据插入临时表或表变量，你将获得更好的性能。鉴于当你没有使用 postdata 中的数据时你注意到了一个改进，这是一个很好的选择，这就是问题所在。



类似这样：

This is not something we're really going to be able to help much with without similar table data to be honest.

In terms of understanding the Execution Plan you may find this CodeProject article useful: SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]

You do appear to have a lot of sub-queries that you may want to try to rationalise. I'll have a another look to see if there is anything else to try.

[UPDATE] I can't prove it, but I'm fairly sure that if you take some of those sub-queries and insert the data into a temporary table or table variable you are going to get better performance. Given that you notices an improved when you are not using the data from postdata it's a good bet that that is the problem.

Something like this:

select glP.fkosControlNumberStatus, glP.SourceJENumber, AccountSegment, 
        sum(case when glP.DistAmount > 0 then glP.DistAmount else 0 end) Debit,
        sum(case when glP.DistAmount < 0 then glP.DistAmount else 0 end) Credit,
        sum(glP.DistAmount) DistAmountTotal
INTO #postdata 
from glPost glP WITH (NOLOCK)
join glAccountingPeriod WITH (NOLOCK) on glAccountingPeriod.pk = glP.fkglAccountingPeriod
join glFiscalYear WITH (NOLOCK) on glFiscalYear.pk = glAccountingPeriod.fkglFiscalYear
join glAccount WITH (NOLOCK) on glAccount.pk = glFiscalYear.fkglAccount
where dbo.fnGetDatePartOnly(glFiscalYear.PeriodEnd) = @YearEndDate
and glP.fkMLSosCodeEntryType = 2202
group by glP.fkosControlNumberStatus, glP.SourceJENumber, AccountSegment

然后

Select Distinct
       glPostExtended.BatchNumber,
       pd.fkosControlNumberStatus,
       pd.AccountSegment Fund,
       pd.Debit,
       pd.Credit,
       pd.DistAmountTotal,
       glPostExtended.SubLedger Module,
       glPost.SourceJENumber,
       glPost.PostToDate
From
       glPost WITH (NOLOCK)
       --This joins limits the glPostExtended Records from duplicating glPost Records for Summarized posting
    join (Select fkglPost, MAX(fkglDist) as fkglDist, COUNT(fkglDist) as RecordCount From glPostExtended WITH (NOLOCK) Group By fkglPost) as glPostExtendedLimiter on glPost.PK = glPostExtendedLimiter.fkglPost
    join #postdata pd on pd.fkosControlNumberStatus = glPost.fkosControlNumberStatus and pd.SourceJENumber = glPost.SourceJENumber
where pd.DistAmountTotal <> 0
  and glPost.fkMLSosCodeEntryType = 2202
order by glPostExtended.BatchNumber, glPost.SourceJENumber, pd.AccountSegment

如果没有别的，那么解释执行计划会更容易！

If nothing else it's going to be easier to interpret the execution plan!

这篇关于如何提高10分钟查询的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！