Performance Problem In a Procedure

  • 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

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

  • 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

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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