Click here to Skip to main content
15,945,119 members
Please Sign up or sign in to vote.
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
Updated 12-May-17 6:30am
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

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!
 
Share this answer
 
v3
Comments
[no name] 12-May-17 12:48pm    
"If nothing else it's going to be easier to interpret the execution plan!":
Which (the execution plan) is a lot of times not easy to Interpret :)
A 5 for all the information
CHill60 12-May-17 12:55pm    
Sometimes I look at an execution plan and exclaim "wtf?!" :)
Thanks for the 5
[no name] 12-May-17 12:58pm    
Thank you for all the help and the information. I am trying to learn to read & understand the Execution plan. Hopefully something goes in my head.
[no name] 12-May-17 13:10pm    
I have a question. When you did the
INTO #postdata
, is that where you created the temporary table postdata? I am looking up what is INTO in sql as I never knew about it before.
CHill60 12-May-17 13:14pm    
Yes - you can create temporary tables from a SELECT in that way. You don't have to create or declare it first. If the table already exists you would use INSERT INTO #postdata (..columns..) SELECT ... etc. I should really have deleted it first in case this was a re-run - see sql - Drop a temporary table if it exists - Stack Overflow[^]
I've also just spotted a bug in my query where postdata.DistAmountTotal <> 0 should be where pd.DistAmountTotal <> 0. I'll fix that now.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900