0

I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick. However, in production it seems to hang and causes blocking of other queries.

Where do I begin to find a solution for this? Below is my code and the plan...

https://www.brentozar.com/pastetheplan/?id=ryAQWfL2X

The issue seems to be with the CTE statement. Is there a more efficient way of writing this?

-- table to store break details
CREATE TABLE #PositionReconciliationCurrentBreaks
(
    TransactionKey INT,
    BreakOriginationDate DATE,
    BreakAge DECIMAL (18,0)
);
TRUNCATE TABLE #PositionReconciliationCurrentBreaks;

WITH PreviousRecBreaks_CTE -- get breaks from previous rec
AS
(
    SELECT 
        MAX(i.InvestmentID) AS InvestmentID,
        PRT.PositionDate,
        PRT.PositionReconciliationDetailKey,
        MAX(PRT.PositionReconciliationTransactionKey) AS PositionReconciliationTransactionKey,
        PRT.InvestmentKey, 
        MAX(PRT.GenevaInvestmentKey) AS GenevaInvestmentKey,
        MAX(PRT.BrokerInvestmentKey) AS BrokerInvestmentKey,
        PRT.PaymentCurrencyKey,
        PRT.AccountKey,
        CASE WHEN MAX(PRT.BreakOriginationDate) = '2100-01-01' THEN NULL ELSE MAX(PRT.BreakOriginationDate) END AS BreakOriginationDate,
        MAX(PRT.BreakAge) AS BreakAge,
        SUM(ISNULL(PRT.GenevaQuantity,0)) AS GenevaQuantity,
        SUM(ISNULL(PRT.BrokerQuantity,0)) AS BrokerQuantity
    FROM [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationTransaction PRT -- get transactions from previous rec
    INNER JOIN 
        [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail TRD -- check details to get Action on transaction
            ON TRD.PositionReconciliationDetailKey = PRT.PositionReconciliationDetailKey -- join on Detail Key
    inner join [Fund.Accounting.Model.TradeReconciliation].Investment i -- add in investmentID for debugging purpose
            ON i.InvestmentKey = PRT.InvestmentKey
    INNER JOIN 
        [Fund.Accounting.Model].[ReconciliationActions] RA -- actions
            ON RA.ReconciliationActionID=TRD.ReconciliationActionID     
            AND (TRD.PositionCommentKey IS NOT NULL) --approved breaks must have a comment.
            AND RA.Name <> 'Suppress' -- ensure not suppressed
            AND RA.Name <> 'Auto Match' -- ensure not perfect match - we do want to include cross references with a qty diff
    WHERE PRT.PositionReconciliationID = @PreviousPositionReconciliationId -- previous rec
    GROUP BY -- added grouping for quantity breaks as x-refs will be 2 separate lines in PositionReconciliationTransaction tbl; We group to force this as 1 line
        PRT.PositionDate,
        PRT.PositionReconciliationDetailKey,
        PRT.InvestmentKey, 
        PRT.PaymentCurrencyKey,
        PRT.AccountKey
),CurrentRecBreaks_CTE -- get breaks from current rec
AS
(
    SELECT  
        MAX(i.InvestmentID) AS InvestmentID,
        PRT.PositionDate,
        PRT.PositionReconciliationDetailKey,
        MAX(PRT.PositionReconciliationTransactionKey) AS PositionReconciliationTransactionKey,
        PRT.InvestmentKey, 
        MAX(PRT.GenevaInvestmentKey) AS GenevaInvestmentKey,
        MAX(PRT.BrokerInvestmentKey) AS BrokerInvestmentKey,
        PRT.PaymentCurrencyKey,
        PRT.AccountKey,
        CASE WHEN MAX(PRT.BreakOriginationDate) = '2100-01-01' THEN NULL ELSE MAX(PRT.BreakOriginationDate) END AS BreakOriginationDate,
        MAX(PRT.BreakAge) AS BreakAge,
        SUM(ISNULL(PRT.GenevaQuantity,0)) AS GenevaQuantity,
        SUM(ISNULL(PRT.BrokerQuantity,0)) AS BrokerQuantity
    FROM [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationTransaction PRT -- get transactions from previous rec
    INNER JOIN 
        [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail TRD -- check details to get Action on transaction
            ON TRD.PositionReconciliationDetailKey = PRT.PositionReconciliationDetailKey -- join on Detail Key
    inner join [Fund.Accounting.Model.TradeReconciliation].Investment i -- add in investmentID for debugging purpose
            ON i.InvestmentKey = PRT.InvestmentKey
    LEFT JOIN 
        [Fund.Accounting.Model].[ReconciliationActions] RA -- actions
            ON RA.ReconciliationActionID=TRD.ReconciliationActionID     
            AND RA.Name <> 'Suppress' -- ensure not suppressed
            AND RA.Name <> 'Auto Match' -- ensure not perfect match - we do want to include cross references with a qty diff
    WHERE PRT.PositionReconciliationID = @PositionReconciliationID -- current rec
    GROUP BY -- added grouping for quantity breaks as x-refs will be 2 separate lines in PositionReconciliationTransaction tbl; We group to force this as 1 line
        PRT.PositionDate,
        PRT.PositionReconciliationDetailKey,
        PRT.InvestmentKey, 
        PRT.PaymentCurrencyKey,
        PRT.AccountKey
)
INSERT INTO #PositionReconciliationCurrentBreaks
(
    TransactionKey, 
    BreakOriginationDate, 
    BreakAge
)   
SELECT  -- get breaks in current rec that also appeared in previous rec
     C.PositionReconciliationTransactionKey
    ,ISNULL(P.BreakOriginationDate,@PreviousPositionReconciliationPeriod) --set ro previous date if it is first time presence.
    ,CASE DATEDIFF(DAY, P.PositionDate, C.PositionDate)
        WHEN  0 THEN 1 --set to 1 day old, if it is 1st time 
        ELSE ISNULL(P.BreakAge,0) + DATEDIFF(DAY, P.PositionDate, C.PositionDate)  -- get no. of days difference between current rec and previous one
    END AS BreakAge
FROM PreviousRecBreaks_CTE P 
INNER JOIN 
    CurrentRecBreaks_CTE C
        ON P.AccountKey = C.AccountKey
            AND P.PaymentCurrencyKey=C.PaymentCurrencyKey
            AND P.InvestmentKey=C.InvestmentKey
            --AND (P.GenevaInvestmentKey=C.GenevaInvestmentKey
            --  OR P.BrokerInvestmentKey=C.BrokerInvestmentKey)
            AND P.GenevaQuantity=C.GenevaQuantity
            AND P.BrokerQuantity=C.BrokerQuantity
WHERE NOT EXISTS(
    -- exclude breaks that are now perfect matches (or suppressed???)
    SELECT
        D.PositionReconciliationDetailKey
    FROM 
        [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail D
    INNER JOIN  -- join on AggregatePositionReconciliationTransaction so we can get the Qty Diff value
        [Fund.Accounting.Facade.PositionReconciliation].[AggregatePositionReconciliationTransaction] (@PositionReconciliationID,NULL,NULL) A
            ON A.PositionReconciliationDetailKey = D.PositionReconciliationDetailKey
    WHERE D.PositionReconciliationID = @PositionReconciliationID -- current rec
        AND A.QuantityDifference = 0 -- perfect matches
        AND D.PositionReconciliationDetailKey = C.PositionReconciliationDetailKey -- join details to transactions
);
0

One pointer I would look at:

There are a lot of Key Lookups happening in the plan: Try adding INCLUDE (PositionReconciliationDetailKey, PositionDate, PaymentCurrencyKey, BreakOriginationDate, BreakAge) to the index [TestReconciliation].[Fund.Accounting.Model.PositionReconciliation].[PositionReconciliationTransaction].[TEST_KOB_02].[PRT].

I may have missed a column or two in this list - you should hover the mouse pointer over each Key Lookup icon and check the "Output" list.

Key lookups happen when SQL Server uses a non-clustered index to speed up the query, but has to go fetch individual rows and columns from the clustered index (the "base table"). This can be horribly inefficient when you're dealing with a lot of rows. Including those columns at the leaf level of the non-clustered index increases the size of the index, but lets SQL Server read everything from the index without having to perform key lookups.

Because you're re-using this lookup with the CTE, I expect this change could give you a good performance benefit.

But to answer the other part of your question: compare the above index between Prod and Test - with comparable amounts of data, you should probably be getting a similar plan on the two servers.

If they are in fact the same, you may be dealing with parameter sniffing issues (which you can solve in a number of different ways, one of them being adding OPTION (RECOMPILE) at the end of your query).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.