CODEWITHSUNDEEP
axaptadynamics-ax-2012x++dynamics-ax-2012-r2
mrsalonen
mrsalonen

Reputation: 371

AX 2012R2: Lookup query takes too long, lookup never opens

I have a AX2012R2 CU6 (build&client 6.2.1000.1437, kernel 6.2.1000.5268) with the following problem:

On AP>Journals>Invoices>Invoice Journal>lines (form LedgerJournalTransVendInvoice), when I select Vendor as Account type and then activate the lookup on the Account field, AX freezes for a couple minutes and when it recovers, the lookup is closed/never opened. This happens every time when account type vendor, other account types work just fine.

I debugged this to LedgerJournalEngine.accountNumLookup() --> VendTable.lookupVendor line

formSegmentedEntryControl.performFormLookup(formRun);

The above process takes up the time.

Any ideas before I hire an exorcist?

Upvotes: 2

Views: 1621

Answers (2)

Tom V
Tom V

Reputation: 1496

There is a known KB for this for R3, look for it on Lifecycle services

KB 3086961 Performance issue of VendorLookup on the volume data, during the GFM Bugbash 6/11 took over 30 minutes

Even though the fix is for R3 it should be easy to backport as the changes are described as

The root cause seemed to be the DirPartyLookupGridView, which had around 14 joins on views and tables. This view is used in many places and hence seemed to have grown quite a lot over time.

The changes in the hotfix remove the view and add only the required datasources - dirpartytable and logisticsaddress to the VendTableLookup form.

The custtableLookup is not using the view and using custom datasource joins instead, so no changes there.

Try implementing that change and see what happens.

I'm not sure this will fix your issue as in your execution plan the only operation that seems really expensive is the sort operator which needs to spill to tempdb (you might need more memory to solve that) but the changes in the datasource could have the effect of removing the sort operator from the execution plan as the data may be sorted by an index.

Upvotes: 6

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

Probably the SQL Server chose the wrong query plan.

First check that you have not disabled any indexes on the involved tables, then do a synchronize on them.

If still a problem, then to run a STATISTICS UPDATE on the involved tables (including the tables in the view).

Upvotes: 2

Related Questions