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
);
Best Answer
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).