2

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?

mrsalonen
  • 343
  • 2
  • 15
  • What query is it executing? Trace it and tune it. – Tom V Dec 16 '15 at 09:46
  • It's basic AX: SELECT FIRSTFAST AccountNum, Party FROM VendTable(VendTable) ORDER BY VendTable.AccountNum ASC JOIN Name, City, Locator, State, ZipCode, CountryRegionId, NameAlias FROM DirPartyLookupGridView(DirPartyLookupGridView) ON VendTable.Party = DirPartyLookupGridView.Party AND ((((DirPartyLookupGridView.AddressValidFrom <= 2015-12-15T14:29:27) && (DirPartyLookupGridView.AddressValidTo >= 2015-12-15T14:29:27)))) – mrsalonen Dec 16 '15 at 10:36
  • Furthermore: I run the view DIRPARTYLOOKUPGRIDVIEW from SQL Server, first 1000 rows. Took three minutes. In a non-R2 environment, five seconds. – mrsalonen Dec 17 '15 at 07:53
  • Can you post the query and execution plan then? Here or on dba.stackexchange.com – Tom V Dec 17 '15 at 08:21
  • Posted a link below. – mrsalonen Dec 17 '15 at 09:29

2 Answers2

6

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.

Tom V
  • 1,498
  • 18
  • 24
  • This seems to be it. Would you know how I can get the info about the changes, so I can make them manually and try it out? – mrsalonen Dec 17 '15 at 10:09
  • 1
    I added a link in the answer. You can search there and there is a "view changes" button. – Tom V Dec 17 '15 at 10:29
  • Alas, the "view changes" is useless. I created a LCS incident to get the changes in XPO. We'll see, but this really seeme to be the answer :) – mrsalonen Dec 17 '15 at 11:36
  • Yeah, this was it. The culprit was view called DirPartyLookupGridView that had grown too large and slow, so in R3 Microsoft removed it as a datasource from form VendTableLookup and added DirPartyTable etc as datasources. Dropped opening the lookup from three minutes to half a second after I made the changes. Another esoteric AX problem solved :) – mrsalonen Dec 21 '15 at 08:00
2

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).

Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
  • Made a project that included all the tables and views affected, found no disabled indexes, synchronized. No effect. SQL Server maintenance plan rebuilds all indexes and updates all statistics nightly. I'm stumped. – mrsalonen Dec 17 '15 at 07:15
  • Use the SQL you provided in a SQL Server Query window then show execution plan etc. I guess you used SQL tracer or this answer to get it: http://stackoverflow.com/a/33927010/4509 – Jan B. Kjeldsen Dec 17 '15 at 07:33
  • The SQL script, execution plan and its XML are in Gdrive: https://drive.google.com/open?id=0B5Qfc-HE9_U1ZnBfRmhMZmRLWWs – mrsalonen Dec 17 '15 at 09:01
  • I suspect the `OR (T2.PARTITION#2 IS NULL)` etc. makes the query slow. This may be a kernel error, that is solved in a newer version of AX. Try searching the issue in LCS or apply a kernel update.I did not understand the XML execution plan. – Jan B. Kjeldsen Dec 17 '15 at 09:23