client not responding due to below code

Finance and Operations/AX Developer Forum
1

delete method in below code is a standard method and for every delete, it clears the tax cache. and execute the cascade delete action. and programs stuck somewhere.How to resolve this?

No of time loop execute / record = 23k

while select TaxTable where TaxTable.TaxCode like ‘TX’
&& TaxTable.Imported == NoYes::Yes
notExists join TaxTrans where TaxTrans.TaxCode == TaxTable.TaxCode

{

select firstonly TaxTrans where TaxTrans.TaxCode == TaxTable.TaxCode;
if(TaxTrans) continue;

select firstonly TaxData where TaxData.TaxCode == TaxTable.TaxCode;

if(!TaxData)
{

TaxTableRecdsDeleted +=1;
TaxTable.delete();

}

}

2

First of all, let me format your code so it’s easier to read. Please do it by yourself next time.

while select taxTable
    where taxTable.TaxCode like 'TX'
       && taxTable.Imported == NoYes::Yes
    notExists join taxTrans
        where taxTrans.TaxCode == taxTable.TaxCode
{
    select firstonly taxTrans
        where TaxTrans.TaxCode == taxTable.TaxCode;
        
    if(taxTrans) continue;

    select firstonly taxData
        where taxData.TaxCode == taxData.TaxCode;

    if(!taxData)
    {
        taxTableRecdsDeleted +=1; 
        taxTable.delete();
    }
}

The second select statement looks unnecessary, therefore let’s throw it away. The last select statement can be added to the while select, therefore we end up with just a single query, making things much more efficient.

while select taxTable
    where taxTable.TaxCode like 'TX'
       && taxTable.Imported == NoYes::Yes
    notExists join taxTrans
        where taxTrans.TaxCode == taxTable.TaxCode
	notExists join taxTable
		where taxData.TaxCode == taxData.TaxCode;
{
	taxTableRecdsDeleted +=1; 
	taxTable.delete();
}

We could even utilize delete_from statement.

Try if you still have the same problem with the optimized query. (By the way, try getting rid of the inefficient ‘like’ filter on TaxCode.)

If so, it sounds like a blocking in database. You can check the current state with the activity monitor, sp_who2 or so, or set up extended events for logging (note that Dynamics Performance Analyzer sets does this setup for you).

3

Thanks Martin

I am sorry the below was my actual query, I was trying not exists join hence, will optimize query provided in your reply behave as below query?

while select taxTable
where taxTable.TaxCode like ‘TX’
&& taxTable.Imported == NoYes::Yes
{
select firstonly taxTrans
where TaxTrans.TaxCode == taxTable.TaxCode;

if(taxTrans) continue;

select firstonly taxData
where taxData.TaxCode == taxData.TaxCode;

if(!taxData)
{
taxTableRecdsDeleted +=1;
taxTable.delete();
}
}

4

It’ll definitely run faster, because it makes just a fraction of database requests.

Regarding whether you’ll still see the same problem, I don’t know, that’s why I asked you to try it. I also suggested what to do next if the problem is still there - please see my previous reply.

5

Hi Martin,

looks like joins are not working correctly .

while select taxTable
where taxTable.TaxCode like ‘TX’
&& taxTable.Imported == NoYes::Yes
{

– Count is 2534

while select taxTable
where taxTable.TaxCode like ‘TX*’
&& taxTable.CM_Imported == NoYes::Yes
notExists join taxTrans
where taxTrans.TaxCode == taxTable.TaxCode

{

Count - 2530

while select taxTable
where taxTable.TaxCode like ‘TX*’
&& taxTable.CM_Imported == NoYes::Yes
notExists join taxTrans
where taxTrans.TaxCode == taxTable.TaxCode
notExists join taxData
where taxData.TaxCode == taxData.TaxCode
{

Count is agin 2534

6

The first query uses completely different conditions than the other two, therefore I’m going to ignore it.

I’ve made a mistake when refactoring your code. Please fix where taxData.TaxCode == taxData.TaxCode to t**axData.TaxCode == taxTable.TaxCode.

7

Hi Martin,

Below loop is selecting correct records to delete, it now just performance issue while deleting records. I look into DB but no lock on the process. Any Reson that job is stick on bold X++ line.

static void Job14(Args _args)
{
TaxTable taxTable;
TaxTrans taxTrans;
TaxData taxData;
int taxTableRecdsDeleted;
int taxTableRecdsDeleted1;
CM_ClearTaxbyState cleartaxbystae;

taxTableRecdsDeleted=0;
taxTableRecdsDeleted1=0;

ttsBegin;
delete_from cleartaxbystae;
ttsCommit;

ttsBegin;
while select forUpdate taxTable
where taxTable.TaxJurisdictionCode like ‘TX*’
&& taxTable.CM_Imported == NoYes::Yes
notExists join taxTrans
where taxTrans.TaxCode == taxTable.TaxCode
{

select firstonly TaxData where TaxData.TaxCode == taxTable.TaxCode;

if(!TaxData)
{
ttsBegin;
cleartaxbystae.clear();
cleartaxbystae.RefRecid=taxTable.RecId;
cleartaxbystae.insert();
//TaxTableRecdsDeleted +=1;
//delete_from taxTable WHERE taxTable.RecId==taxTable.RecId;

// TaxTable.delete();
ttsCommit;

}
}

delete_from taxTable
exists join cleartaxbystae where taxTable.RecId==cleartaxbystae.RefRecid;

TaxTableRecdsDeleted=taxTable.RowCount();

ttscomit;

}

8

I’m missing your definition of “is stuck”. It’s seems that you’re now saying that the query is just slow, not that it’s blocked.

Causes of performance problems are hard to guess; a much better approach is measuring what’s going on. For example, you may have a delete action without an index and the full-table scan takes a lot of time. But that’s just one of many possibilities - you should measure what’s going on in your particular system.