while select notexists join behavior in runCalculationsForBpMissingCost

(0) Share

Report

Posted on by Steeve Gilbert

1,501

It all started with performance issue while creating a batch order (Estimation part). I end up finding that

\Classes\PmfRecycledBpMissingCostCalc_Prod\runCalculationsForBpMissingCost

was taking a long time. I debug this while select and I saw that i was browsing ProdIds that were different then the where clause should filter. So I created a job to replicate and understand. Turns out that just changing the position of the where clause change the behavior. Below, you'll see that the first "while select" will scroll thru many ProdId, and the second "while select" will only browse the specified prodId (523464). I though those 2 syntax were equal!

PmfProdCoBy                 pmfProdCoBy;
PmfCoByProdCalcTrans        pmfCoByProdCalcTrans;
BOMCalcProd_PmfCoByProduct  byProductCalc;

while select pmfProdCoBy
notexists join pmfCoByProdCalcTrans
where pmfProdCoBy.ProdId == '523464'
        &&  pmfCoByProdCalcTrans.PmfIdRefCoByRecId == pmfProdCoBy.RecId
{
    info(pmfProdCoBy.ProdId);
}
    
warning("2 where");
    
while select pmfProdCoBy
    where pmfProdCoBy.ProdId == '523464'        
notexists join pmfCoByProdCalcTrans    
    where pmfCoByProdCalcTrans.PmfIdRefCoByRecId == pmfProdCoBy.RecId
{
    info(pmfProdCoBy.ProdId);
}

I don't know how this is translated to T-SQL, but from Ax point of view it looks the same. Can someone explain this?

Second question : Browsing all those ProdId, like the first while do, is this then intended purpose of runCalculationsForBpMissingCost? Because I don't see why you would browse all those prodId to Estimate only 1.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Mea_ 60,284 on at

Like (0)

Report
RE: while select notexists join behavior in runCalculationsForBpMissingCost

Hi Steeve,

It looks like a bug for me. If you will go to Tools-> Oprions -> Sql tab and tick "SQL Trace" tick box and "Infolog" tick box in Multiply SQL statement group you will be able to see all T-SQL statements.

So first statement will be

SELECT ... FROM PMFPRODCOBY T1

WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'rcnz'))

AND NOT (EXISTS (SELECT 'x' FROM PMFCOBYPRODCALCTRANS T2 WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'rcnz')) AND ((T1.PRODID=?) AND (T2.PMFIDREFCOBYRECID=T1.RECID)))))

and second

SELECT ... FROM PMFPRODCOBY T1
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'rcnz')) AND (T1.PRODID=?)) AND NOT (EXISTS (SELECT 'x' FROM PMFCOBYPRODCALCTRANS T2
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'rcnz')) AND (T2.PMFIDREFCOBYRECID=T1.RECID))))

As you can see first one select all PMFPRODCOBY that does not have PMFCOBYPRODCALCTRANS for selected prod id
and second one selects PMFPRODCOBY for selected prod id.

Was this reply helpful? Yes No
Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: while select notexists join behavior in runCalculationsForBpMissingCost

In addition to Ievgen's advice, you can use generateOnly keyword together with getSqlStatement() to see the actual SQL query. It's very helpful when debugging cases like this.

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (0)

Report

RE: while select notexists join behavior in runCalculationsForBpMissingCost

Thanks for your answers guys!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: while select notexists join behavior in runCalculationsForBpMissingCost

hello Steeve ¡¡

What was the solution to your problem? I have the same problem :(

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (1)

Report
RE: while select notexists join behavior in runCalculationsForBpMissingCost

Hi Denisse,

I reported this back when we were migrating to Ax2012. But now we are migrating to D365 and it is fixed, the "where" as been moved like I expected it to be. Here's how it looks in D365 :

public void runCalculationsForBpMissingCost() { PmfProdCoBy pmfProdCoBy; PmfCoByProdCalcTrans pmfCoByProdCalcTrans; BOMCalcProd_PmfCoByProduct byProductCalc; while select pmfProdCoBy where pmfProdCoBy.ProdId == prodParmBOMCalc.topMostProdTableCalc().ProdId notexists join pmfCoByProdCalcTrans where pmfCoByProdCalcTrans.PmfIdRefCoByRecId == pmfProdCoBy.RecId { byProductCalc = BOMCalcProd_PmfCoByProduct::newCoByProduct( pmfProdCoBy, prodParmBOMCalc.ProfitSet, prodParmBOMCalc.CalcDate, prodParmBOMCalc.References, true); byProductCalc.calc(); } }

There's probably a patch that was release for Ax2012 to fix that. Check out LCS or contact your partner.

Was this reply helpful? Yes No
Ahmed Issa 249 on at

Like (0)

Report

RE: while select notexists join behavior in runCalculationsForBpMissingCost

Hi Steeve ,

We tried your suggestion of swapping the where clause and it works in AX 2012 R3 CU13.

We will investigate the consequences of the change and will post soon.

Was this reply helpful? Yes No