How to improve the performance of a 10 min query?

Question

0.00/5 (No votes)

See more:

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....

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

Posted 12-May-17 5:39am

Truecolors

Updated 12-May-17 6:30am

Add a Solution

Comments

ZurdoDev 12-May-17 12:24pm

You need to refer to the Execution Plan. There is no way we can know what is causing it to take long.

RickZeeland 12-May-17 13:09pm

I once had a mysterious problem like this, rebuilding / reorganizing the indexes helped.
See http://www.sql-server-performance.com/2007/rebuilding-indexes/

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-12T06:30:00

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:

SQL

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

then

SQL

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!