March 22, 2013 at 4:47 am
Hi,
I feel sorry that in my previous post I put very limited information about my question and apologize for the same. So I am putting as much information as possible about tables, indexes, views etc.
I have a procedure whose performance is pretty bad. I execute it using the following statement
exec USP_ChartofAccountsDetail
@numDomainID=163,@dtToDate='2013-03-21 07:21:43:563',@vcAccountId='5076,5080'
I am attaching the procedure schema, table\index schema and the execution plan as vital components.
On a side note, I do believe that there is a view inside the procedure having many left and right joins which are not so great but are needed when we have data distribution in a particular fashion.
Please give me few inputs so that I can try making this better.
Regards
Chandan Jha
March 22, 2013 at 6:44 am
The single biggest cost is a function which references a view which references a function which unnecessarily uses a slow cursor to concatenate some columns. Or something similar. A half day's worth of work for someone who knows what they are doing, with full access to your data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 22, 2013 at 6:53 am
First thing, looking at the SELECT operator in your execution plan, you have a timeout. This means that the optimizer gave up on attempting to find a good enough plan for your query. This generally leads to poor performance. It's caused by overly complex queries that the optimizer can't process fast enough. So, before we look to anything else, our first problem is that this plan is unstable and could change over time.
You're joining between a table, Chart_Of_Accounts, and a view, VIEW_GENERALLEDGER. I suspect that we're looking at problems within the view. The optimizer will take the view and attempt to simplify it, eliminating tables and joins that are not needed to satisfy the query you're running. Your view hits 15 different objects, including two other views (nesting views is ALWAYS a problem), further adding to the complexity of this query. But if we look at the execution plan, it's only hitting eleven objects. Clearly, you can simplify this query best by eliminating these views and simply write the query to retrieve the information you need. SQL Server just doesn't lend itself to code reuse. It's not a programming language.
Finally, the SplitIDs function is not costing 0% as suggested by this execution plan. It can be replaced by using a tally table. If you do a search on tally table and the words 'Jeff Moden' here on SSC you can find a good replacement.
I didn't bother looking into whether or not you have good indexes in place because we need to get to a stable plan first. Rewrite the query to get rid of the views. Then you can worry about indexes & statistics and all the rest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2013 at 7:32 am
Chris\ Grant,
Thanks a lot for replying. I agree with you without any reservation. I know that the plan is not coming good due to complex view involved in it. I hate views because developers mistake it for tables and go on increasing the complexity by nesting them as deep as they can. Repeating the same code again and again for a similar business logic makes more sense to me because it is easier to troubleshoot.
Even though the tables are very very small but surprisingly it is taking a hit.
I will try to re-write the query myself by concentrating on objects actually needed as showsn in execution plan so that indexes and statistics can be looked afterwards.
Chandan Jha
March 22, 2013 at 7:42 am
Grant,
As a side note, can you please let me know how to find the tables actually involved in the execution plan so that I can filter the unused ones. Do I need to go through the XML format of the plan and look each line or is there a better way?
Regards
Chandan Jha
March 22, 2013 at 7:57 am
Most folks put in this situation will look for a quick win to buy the time necessary for the complete overhaul which Grant correctly recommends.
I think you have some scope for a quick win here by rewriting the function IF_GetPaymentBizDoc (referenced by the view VIEW_BIZPAYMENT) as an inline table-valued function.
Here's the rewritten function:
CREATE FUNCTION [dbo].[IF_GetPaymentBizDoc]
-- Replaces fn_GetPaymentBizDoc
(@numBizDocsPaymentDetId NUMERIC(8),
@numDomainId INT)
RETURNS TABLE AS
RETURN
WITH SourceData AS (
SELECT
Amount= cast(SUM(monAmount) as varchar(50)),
numCheckNo= isnull( CAST(numCheckNo AS VARCHAR(50)),'No-Chek'),
vcReference = isnull(vcReference,'No-Ref'),
vcMemo= isnull(vcMemo,'No-Memo' )
FROM VIEW_BIZDOCPAYMENT -- no definition in scripts
WHERE numBizDocsPaymentDetId = @numBizDocsPaymentDetId
AND numDomainId = @numDomainId
GROUP BY numCheckNo, vcReference, vcMemo
)
SELECT Narration = STUFF((
SELECT ',' +
'Ref: ' + vcReference +
' Memo: ' + vcMemo +
' Chek No: ' + numCheckNo +
' Amount: ' + Amount
FROM SourceData
ORDER BY numCheckNo, vcReference, vcMemo
FOR XML PATH('')),1,1,'')
Here's the rewritten view which references it:
ALTER VIEW [dbo].[VIEW_BIZPAYMENT]
AS
SELECT
o.numDomainId,
o.numBizDocsPaymentDetId,
x.Narration
FROM OpportunityBizDocsDetails o
CROSS APPLY dbo.IF_GetPaymentBizDoc (numBizDocsPaymentDetId, numDomainId) x
Note that the iTVF itself references a view which doesn't appear in your script. If you can find the view definition then sub it into the iTVF. With this done, you might consider reconstructing the iTVF so that it replaces VIEW_BIZPAYMENT completely.
This part of the query is related to the nested loops left outer join costing 68% of the total, it's well worth tackling.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 22, 2013 at 8:33 am
It's difficult to tell with all of those funky joins and no sample data, but you might get a win with this too;
SELECT CAST(ID AS NUMERIC(9))
INTO #Accounts
FROM dbo.SplitIDs(@vcAccountId, ',')
SELECT
h.numDomainId,
coa.numAccountId,
CompanyName = ci.vcCompanyName,
dm.numDivisionID,
0 AS Opening,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numDebitAmt, 0))) TotalDebit,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numCreditAmt, 0))) TotalCredit,
(SUM(ISNULL(d.numDebitAmt, 0)) - SUM(ISNULL(d.numCreditAmt, 0))) AS Closing,
COA.numParntAcntTypeId
FROM dbo.General_Journal_Header h
INNER JOIN dbo.General_Journal_Details d
ON h.numJournal_Id = d.numJournalId
LEFT JOIN dbo.Chart_Of_Accounts coa
ON d.numChartAcntId = coa.numAccountId
LEFT JOIN dbo.DivisionMaster dm
LEFT JOIN dbo.CompanyInfo ci
ON dm.numCompanyID = ci.numCompanyId
ON d.numCustomerId = dm.numDivisionID
WHERE
h.numDomainId = @numDomainID
AND dm.numDivisionID IS NOT null -- converts dm to IJ
AND coa.numAccountId IN (SELECT ID FROM #Accounts)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 22, 2013 at 8:56 am
chandan_jha18 (3/22/2013)
Grant,As a side note, can you please let me know how to find the tables actually involved in the execution plan so that I can filter the unused ones. Do I need to go through the XML format of the plan and look each line or is there a better way?
Regards
Chandan Jha
Well, you could walk through the XML... blech. Since it's a pretty simple execution plan with only 11 objects, you can just look at each one in the plan itself to identify where they're coming from.
But, if you want to automate it, you can query the XML directly using XPath/Xquery language within T-SQL. I don't have an example for exactly this requirement, but I have a number of examples up on my blog.
Or, you can also try using string manipulation like I do in this blog post[/url]. Again, not exactly what you're looking for, but it gives you the general idea.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2013 at 5:58 am
ChrisM@Work (3/22/2013)
It's difficult to tell with all of those funky joins and no sample data, but you might get a win with this too;
SELECT CAST(ID AS NUMERIC(9))INTO #Accounts
FROM dbo.SplitIDs(@vcAccountId, ',')
SELECT
h.numDomainId,
coa.numAccountId,
CompanyName = ci.vcCompanyName,
dm.numDivisionID,
0 AS Opening,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numDebitAmt, 0))) TotalDebit,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numCreditAmt, 0))) TotalCredit,
(SUM(ISNULL(d.numDebitAmt, 0)) - SUM(ISNULL(d.numCreditAmt, 0))) AS Closing,
COA.numParntAcntTypeId
FROM dbo.General_Journal_Header h
INNER JOIN dbo.General_Journal_Details d
ON h.numJournal_Id = d.numJournalId
LEFT JOIN dbo.Chart_Of_Accounts coa
ON d.numChartAcntId = coa.numAccountId
LEFT JOIN dbo.DivisionMaster dm
LEFT JOIN dbo.CompanyInfo ci
ON dm.numCompanyID = ci.numCompanyId
ON d.numCustomerId = dm.numDivisionID
WHERE
h.numDomainId = @numDomainID
AND dm.numDivisionID IS NOT null -- converts dm to IJ
AND coa.numAccountId IN (SELECT ID FROM #Accounts)
Chris,
Thank you very very much for giving your precious time to write the code, due to time zone differences, I could not thank you earlier so forgive me for that.
Let me tell you that the above code that you have given as a direct substitute for the procedure is giving me correct results after I tested it for 3 different set of parameters. In another post, the way you changed the function to an inline function returning the data as a table rather than a cursor looks great too.
I do a question at this juncture:
The above code looks very neat as it does not use the big view which has a lot of unnecessary tables and uses tables for most of the tasks. However as suggested by Grant earlier, I looked at the execution plan of original query to see what tables the optimizer is actually using but in your query it does not uses all of them and skips few which are in execution plan.
So how did you determine the table relationships to reach the actual joins between least sufficient tables. Is there a way I can learn this as it will help me think like an efficient developer?
Please guide me at this.
Regards
Chandan Jha
March 25, 2013 at 7:31 am
Chandan, you're very welcome, thank you for your generous feedback.
This exercise, resolving a simple query from a more complex query referencing many more objects, often isn't successful because of cardinality differences between tables. In this case we were lucky as your findings indicate that is has worked, although I'm sure you've had to make a few small changes.
Typically the approach is most likely to work if left-joined tables introduce only a single row to the rest of the query. If a left-joined table marked for removal from the query returns more than one row per row on the left hand side (the FROM-list prior to the table), then removing it will eliminate rows from the output, which would then be incorrect.
I started by reformatting the view definition for VIEW_GENERALLEDGER to make it easier to read, then marked the required output columns and their source tables in the FROM-list, as follows;
SELECT
h.numJournal_Id,
h.datEntry_Date,
h.varDescription,
ISNULL(dbo.VIEW_BIZPAYMENT.Narration,
h.varDescription) AS BizPayment,
d.numTransactionId,
CASE
WHEN isnull(h.numReturnID, 0) <> 0
THEN ' Chek No: ' + isnull( CAST(CH.numCheckNo AS VARCHAR(50)),'No-Cheq')
ELSE dbo.VIEW_JOURNALCHEQ.Narration END AS CheqNo,
h.numDomainId, -- ########################
'Biz Doc Id: ' + dbo.OpportunityBizDocs.vcBizDocID AS BizDocID,
h.numOppBizDocsId,
d.vcReference AS TranRef,
d.varDescription AS TranDesc,
d.numDebitAmt, -- ############################
d.numCreditAmt, -- ###############################
coa.numAccountId, -- #################################
coa.vcAccountName,
CASE
WHEN isnull(h.numCheckHeaderID, 0) <> 0 THEN + 'Checks'
WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 0 THEN 'Cash'
WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 1 AND ccc.bitChargeBack = 1 THEN 'Charge'
WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 1 THEN 'Deposit'
WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 2 THEN 'Receved Pmt'
--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN ''Receive Amt''
--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN ''Vendor Amt''
WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN 'BizDocs Invoice'
WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN 'BizDocs Purchase'
WHEN isnull(h.numCategoryHDRID, 0) <> 0 THEN 'Time And Expenses'
WHEN isnull(h.numBillID, 0) <> 0 THEN 'Bill'
WHEN isnull(h.numBillPaymentID, 0) <> 0 THEN 'Pay Bill'
WHEN isnull(h.numReturnID, 0) <> 0 THEN
CASE WHEN RH.tintReturnType=1 AND RH.tintReceiveType=1 THEN 'Sales Return Refund'
WHEN RH.tintReturnType=1 AND RH.tintReceiveType=2 THEN 'Sales Return Credit Memo'
WHEN RH.tintReturnType=2 AND RH.tintReceiveType=2 THEN 'Purchase Return Credit Memo'
WHEN RH.tintReturnType=3 AND RH.tintReceiveType=2 THEN 'Credit Memo'
WHEN RH.tintReturnType=4 AND RH.tintReceiveType=1 THEN 'Refund' END
WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) = 0 THEN 'PO Fulfillment'
WHEN h.numJournal_Id <> 0 THEN 'Journal' END AS TransactionType,
dbo.CompanyInfo.vcCompanyName AS CompanyName, -- ###############################
h.numCheckHeaderID,
h.numCashCreditCardId,
h.numOppId,
h.numDepositId,
h.numCategoryHDRID,
dbo.TimeAndExpense.tintTEType,
dbo.TimeAndExpense.numCategory,
dbo.TimeAndExpense.dtFromDate,
dbo.TimeAndExpense.numUserCntID,
dbo.DivisionMaster.numDivisionID, -- ################################
ISNULL(d.bitCleared,0) AS bitCleared,
ISNULL(d.bitReconcile,0) AS bitReconcile,
isnull(h.numBillID, 0) AS numBillID,
ISNULL(h.numBillPaymentID,0) AS numBillPaymentID,
ISNULL(d.numClassID,0) AS numClassID,
ISNULL(d.numProjectID,0) AS numProjectID,
isnull(h.numReturnID, 0) AS numReturnID,
d.numCurrencyID
FROM dbo.General_Journal_Header h -- KEEP
INNER JOIN dbo.General_Journal_Details d -- KEEP
ON h.numJournal_Id = d.numJournalId
INNER JOIN dbo.Chart_Of_Accounts coa -- KEEP
ON d.numChartAcntId = coa.numAccountId
LEFT OUTER JOIN dbo.TimeAndExpense
ON h.numCategoryHDRID = dbo.TimeAndExpense.numCategoryHDRID
LEFT OUTER JOIN dbo.DivisionMaster -- KEEP
LEFT OUTER JOIN dbo.CompanyInfo -- KEEP
ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId
ON d.numCustomerId = dbo.DivisionMaster.numDivisionID
LEFT OUTER JOIN dbo.OpportunityMaster
ON h.numOppId = dbo.OpportunityMaster.numOppId
LEFT OUTER JOIN dbo.CashCreditCardDetails ccc
ON h.numCashCreditCardId = ccc.numCashCreditId
LEFT OUTER JOIN dbo.OpportunityBizDocs
ON h.numOppBizDocsId = dbo.OpportunityBizDocs.numOppBizDocsId
LEFT OUTER JOIN dbo.VIEW_JOURNALCHEQ
ON (h.numCheckHeaderID = dbo.VIEW_JOURNALCHEQ.numCheckHeaderID
or (h.numBillPaymentID=dbo.VIEW_JOURNALCHEQ.numReferenceID and dbo.VIEW_JOURNALCHEQ.tintReferenceType=8))
LEFT OUTER JOIN dbo.VIEW_BIZPAYMENT
ON h.numBizDocsPaymentDetId = dbo.VIEW_BIZPAYMENT.numBizDocsPaymentDetId
LEFT OUTER JOIN dbo.DepositMaster DM
ON DM.numDepositId = h.numDepositId
LEFT OUTER JOIN dbo.ReturnHeader RH
ON RH.numReturnHeaderID = h.numReturnID
LEFT OUTER JOIN dbo.CheckHeader CH
ON CH.numReferenceID = RH.numReturnHeaderID AND CH.tintReferenceType=10
Finally I checked that Chart of Accounts (coa) was referenced only once in the FROMlist.
That's pretty much it. Remove all the stuff you don't need, make sure all the necessary references are there, and give it a whirl.
Don't forget the comments in the code. There's a join order enforcement
LEFT OUTER JOIN dbo.DivisionMaster -- KEEP
LEFT OUTER JOIN dbo.CompanyInfo -- KEEP
ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId
ON d.numCustomerId = dbo.DivisionMaster.numDivisionID
which is designed to ensure a left join between DivisionMaster and General_Journal_Details and an inner join between CompanyInfo and DivisionMaster, however the join between DivisionMaster and General_Journal_Details is converted into an inner join by the WHERE clause: AND dm.numDivisionID IS NOT null -- converts dm to IJ
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply