Post reply

TSQL query suddenly taking hours to run

  • November 2, 2018 at 12:26 pm

    #409253

    Hi,
    I have a query that has suddenly started taking hours to run instead of a few minutes.  I have tried updating statistics and forcing a recompile but it's still performing really badly.  

    Looking at the query plan, I can see that there are a number of large table scans, however of particular concern is a nested loop join where one side of the join contains  37.5m unordered rows (BB04_dbo_BB04_Distribution) and the other side is just 1 row - this seems to be linked to the OR in the join.  There are also a number of type conversion warnings, although I'm not convinced that this is the issue as the query has been fine for the last 8 months.

    The query plan is attached.  This is the query:

    select
        pl.PaymentLineCreatedDateTime ModifiedDateTime,
        0 PaymentLineId_BB01,
        dol.DistributionOrderLineId DistributionOrderLineId_BB04,
        pl.PaymentLineCreatedDateTime CreatedDateTime,
        ISNULL(fp.FundraiserPaymentId ,0) PaymentId_BB01,
        pl.PaymentId PaymentId_BB04,
        0 [NetCommissions],
        0 [NetPayment],
        0 [NetProcessingFees],
        0 [VAT],
        0 NetDonationAmount,
        CONVERT(INT, CONVERT(VARCHAR, ISNULL(p.PaymentDateTime,'1900-01-01'), 112)) DatePostedKey,
        0 CharityId_BB01, --Looked up from FactDonationFms
        'N' isUnDone,
        0 CommissionPayerCompanyId ,
        0 PaymentDestinationId_BB01,
        ra.PaymentDestinationId PaymentDestinationId_BB04,
        0 PaymentBatchId_BB01,
        --CAST(ISNULL(o2.ExternalOrderRef, o.ExternalOrderRef) AS INT) DonationId_BB01,
        CASE WHEN d.DistributionPaymentReclaimLineId <> 0 THEN 1 ELSE 0 END IsRefund,
        /* The payment rate is overridden with 0 in a subsequent Update task if no payment fee is actually deducted from the payment */
        CASE WHEN sub.Deduct = 1 THEN CAST(CAST(ISNULL(sub.Percentage,0) AS NUMERIC(3,2)) AS NVARCHAR(10)) ELSE '0.00' END +'%' [PaymentProcessingFeeRate],
        sub.Deduct DeductFees,
        COALESCE(pvoR.JustGivingTransactionFeeAmount, pvo.JustGivingTransactionFeeAmount, 0) JustGivingTransactionFeeAmount,
        COALESCE(pvoR.JustGivingTransactionFeeRate, pvo.JustGivingTransactionFeeRate,'') JustGivingTransactionFeeRate,
        COALESCE(pvoR.PaymentProcessingFeeAmount, pvo.PaymentProcessingFeeAmount, 0) PaymentProcessingFeeAmount

    from dbo.BB04_dbo_BB04_PaymentLine pl
    JOIN dbo.BB04_dbo_BB04_Payment p ON pl.PaymentId = p.PaymentId
    JOIN dbo.BB04_dbo_BB04_Distribution d ON pl.PaymentLineId = d.DistributionPaymentLineId OR pl.PaymentLineId = d.DistributionPaymentReclaimLineId
    JOIN dbo.BB04_dbo_BB04_DistributionOrderLine dol ON dol.DistributionId = d.DistributionId
    JOIN dbo.BB04_dbo_BB04_OrderLine ol ON ol.OrderLineId = dol.OrderLineId
    JOIN dbo.BB04_dbo_BB04_Order o ON o.OrderId = ol.OrderId
    JOIN dbo.BB04_dbo_BB04_RecipientAccount ra ON p.RecipientAccountId = ra.RecipientAccountId

    LEFT JOIN dbo.BB04_dbo_BB04_RemittanceLine rl ON rl.ExternalOrderRef = o.ExternalOrderRef AND rl.RemittanceLineTypeId = 1 --Payment
    LEFT JOIN (
    SELECT TransactionEntityId, MAX(RevenueScheduleLineId) RevenueScheduleLineId
    FROM dbo.BB04_dbo_BB04_RevenueScheduleItem
    WHERE TransactionEntityClass = 'Remittanceline'
    GROUP BY TransactionEntityId
    ) rsi
    ON rl.RemittanceLineId = rsi.TransactionEntityId
    LEFT JOIN dbo.BB04_dbo_BB04_RevenueScheduleLine RSL ON RSI.RevenueScheduleLineId = RSL.RevenueScheduleLineId
    LEFT JOIN dbo.BB04_dbo_BB04_RevenueSubscriptionLine sub on RSL.RevenueSubscriptionLineId = sub.RevenueSubscriptionLineId

    LEFT JOIN dbo.BB04_dbo_BB04_PaymentCombinerReportLine pcrl ON pcrl.PaymentId_BB04 = p.PaymentId AND pcrl.OdsIsDeleted = 0
    LEFT JOIN dbo.BB01_dbo_BB01_FundraiserPayment fp ON pcrl.ActivityId = fp.ActivityId

    /*Get DonationId for Refunds*/
    LEFT JOIN dbo.BB04_dbo_BB04_MatchingContribution c ON o.ExternalOrderRef LIKE 'MC:%' AND c.MatchingContributionId = CONVERT([int],substring(o.ExternalOrderRef,charindex('/',o.ExternalOrderRef)+(1),len(o.ExternalOrderRef)))
    LEFT JOIN dbo.BB04_dbo_BB04_OrderLine ol2 ON ol2.OrderLineId = c.MatchedOrderLineId
    LEFT JOIN dbo.BB04_dbo_BB04_Order o2 ON o2.OrderId = ol2.OrderId

    LEFT JOIN dbo.MANUAL_PaymentValueOverrides pvoR ON pvoR.PaymentId = pl.PaymentId AND pvoR.DonationId_BB01 = ISNULL(o2.ExternalOrderRef, o.ExternalOrderRef) AND pvoR.IsRefund = 1 AND d.DistributionPaymentReclaimLineId <> 0
    LEFT JOIN dbo.MANUAL_PaymentValueOverrides pvo ON pvo.PaymentId = pl.PaymentId AND pvo.DonationId_BB01 = o.ExternalOrderRef AND pvo.IsRefund = 0 AND d.DistributionPaymentReclaimLineId = 0

    where pl.PaymentLineIsDeleted = 0
    AND p.PaymentTypeId = 1 --Donation
    AND p.PaymentDateTime >= '2016-07-01'
    --Exclude simultaneous payment and refund
    and (d.DistributionPaymentLineId = 0 OR d.DistributionPaymentReclaimLineId = 0)
    AND EXISTS (SELECT 1 FROM Staging.FactPaymentLine_Modified m WHERE m.PaymentId = pl.PaymentId AND m.[Source] = 'BB04')

    I'm not an expert on performance tuning and so I'd really appreciate it if someone could take a look at the plan and let me know what the issue might be before I start refactoring the query 🙂

    Thanks

  • November 2, 2018 at 2:08 pm

    #2012278

    Try adding the suggested index.

  • November 3, 2018 at 12:22 pm

    #2012314

    Before you try anything, you said this problem started recently.  The first thing you should do is make sure your statistics are up do date.

    This is also an "all in one" query with quite a few tables and some derived tables (like a view) that are being joined on.  I'd recommend that someone that is good with performance issues sit down with this code and try a little "Divide'n'Conquer" on it.  The problem isn't going to get any better on it's own.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 4, 2018 at 6:54 am

    #2012332

    First thing to test is how the query performs without the parallel execution plan, append "OPTION (MAXDOP 1)" to the query to test it. 
    😎
    In the execution plan, there are several sources feeding millions and tens of million rows, certainly you should start looking at those as the final cardinality is meager.

  • November 4, 2018 at 7:35 am

    #2012334

    p.s.  You didn't make the mistake of shrinking your database files, did you?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 4, 2018 at 7:48 am

    #2012336

    Jeff Moden - Sunday, November 4, 2018 7:35 AM

    p.s.  You didn't make the mistake of shrinking your database files, did you?

    He he, that's what we call adding an insult to the injury 😉
    😎

  • November 4, 2018 at 7:55 am

    #2012338

    Eirikur Eiriksson - Sunday, November 4, 2018 7:48 AM

    Jeff Moden - Sunday, November 4, 2018 7:35 AM

    p.s.  You didn't make the mistake of shrinking your database files, did you?

    He he, that's what we call adding an insult to the injury 😉
    😎

    yep yep.  That would make for a bad day.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • November 4, 2018 at 2:44 pm

    #2012348

    Thanks for the replies guys, much appreciated. 

    I've tried adding the suggested index, so I'll have to wait and see how that goes.  The statistics on the database are set to auto update, although if the problem persists I will rebuild them manually.  I suspect though that the query would benefit from some refactoring anyway. I'll also try the MAXDOP 1 suggestion.

    No-one has shrunk the database files as far as I know (although I will check) 😀

  • November 4, 2018 at 4:06 pm

    #2012350

    Wolfmeister - Sunday, November 4, 2018 2:44 PM

    Thanks for the replies guys, much appreciated. 

    I've tried adding the suggested index, so I'll have to wait and see how that goes.  The statistics on the database are set to auto update, although if the problem persists I will rebuild them manually.  I suspect though that the query would benefit from some refactoring anyway. I'll also try the MAXDOP 1 suggestion.

    No-one has shrunk the database files as far as I know (although I will check) 😀

    Heh... adding an index is a heck of a way to rebuild stats. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply