如何提高10分钟查询的性能？

本文介绍了如何提高10分钟查询的性能？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个查询，大约需要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

推荐答案

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分钟查询的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！