Use exists join and inner join in axapta

In fact, I also found this problem,

However, junevoful has already been detailed. I will add two points:

1. Data in the second table cannot be referenced using exists join, indicating that the condition is met once the data in the second table exists, however, using inner join can reference the data in the second table, which is an internal join.

2. You can use the SQL Performance Monitor provided by axapta to monitor the execution efficiency of SQL statements. The startup method is as follows:

 

 

Next I will refer to junevoful's article. He is very detailed and wonderful. Thank you for bringing me such a good article!

 

A few days ago, during system optimization, I found that the while nested loop statement exists in the code.

While select table 1

{

..

While select Table2

I didn't care too much in the past, but since we wanted system optimization, we were afraid that we could not find anything to change.

Suddenly, why don't you test the system overhead? So I wrote three jobs for testing.

Static void testinnerjoinusingwhile (ARGs _ ARGs)

{

...

;

Starttime = winapi: gettickcount ();

While select ledgertrans

Where ledgertrans. accountnum = accountnum &&

(Ledgertrans. transdate> = 1/7/2005 &&

Ledgertrans. transdate <= 31/7/2005 ))

{

While select projtransposting

Where projtransposting. Voucher = ledgertrans. Voucher &&

Projtransposting. Account = accountnum

{

...

Tmpfrd_ledgerdridwncontractdtls.insert ();

 

}

}

Endtime = winapi: gettickcount ();

Duration = endtime-starttime;

Info (int2str (duration ));

}

 

Static void testinnerjoinusingjoin (ARGs _ ARGs)

{

While select ledgertrans

Where...

Join projtransposting

Where...

}

 

Static void testexistsjoinusingjoin (ARGs _ ARGs)

{

...

While select ledgertrans

Where...

Exists join projtransposting

Where...

}

It is found that the nested while time is 4012 microseconds, inner join is 1986 microseconds, and exists join is 1689 microseconds.

It can be seen that when writing code, you still need to follow the best practice requirements to achieve the best performance.