Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Problem In a Procedure Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 4:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:29 PM
Points: 459, Visits: 1,889
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


  Post Attachments 
plan.sqlplan (8 views, 110.36 KB)
Table_Schema.txt (4 views, 38.33 KB)
Procedure_Schema.txt (6 views, 2.33 KB)
Post #1434193
Posted Friday, March 22, 2013 6:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 7,133, Visits: 13,520
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434238
Posted Friday, March 22, 2013 6:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 15,556, Visits: 27,928
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1434243
Posted Friday, March 22, 2013 7:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:29 PM
Points: 459, Visits: 1,889
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
Post #1434267
Posted Friday, March 22, 2013 7:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:29 PM
Points: 459, Visits: 1,889
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
Post #1434272
Posted Friday, March 22, 2013 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 7,133, Visits: 13,520
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434282
Posted Friday, March 22, 2013 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 7,133, Visits: 13,520
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434302
Posted Friday, March 22, 2013 8:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 15,556, Visits: 27,928
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. 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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1434314
Posted Monday, March 25, 2013 5:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:29 PM
Points: 459, Visits: 1,889
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





Post #1434847
Posted Monday, March 25, 2013 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 7,133, Visits: 13,520
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse