CODEWITHSUNDEEP
axaptax++morph-x
afk
afk

Reputation: 43

CustTableListPage filtering is too slow

When I'm trying to filter CustAccount field on CustTableListPage it's taking too long to filter. On the other fields there is no latency. I'm trying to filter just part of account number like "*123". I have done reindexing for custtable and also updated statics but not appreciable difference at all. When i have added listpage's query in a view it's filtering custAccount field normally like the other fields. Any suggestion? Edit: Our version is AX 2012 r2 cu8, not a user based problem it occurs for every user, Interaction class has some custimizations but just for setting some buttons enable/disable props. etc... i tryed to look query execution what i found is not clear. something like FETCH_API_CURSOR_000000..x

Upvotes: 2

Views: 498

Answers (3)

afk
afk

Reputation: 43

I've solve the problem. CustTableListPage query had a sorting over DirPartyTable.Name field. When I remove this sorting, filtering with wildcard working like a charm.

Upvotes: 1

Sander
Sander

Reputation: 4042

Keep in mind that that wildcards (such as *) have to be used with care. Using a filter string that starts with a wildcard kills all performance because the SQL indexes cannot be used.

Using a wildcard at the end

Imagine that you have a dictionnary and have to list all the words starting with 'Foo'. You can skip all entries before 'F', then all those before 'Fo', then all those before 'Foo' and start your result list from there.

Similarly, asking the underlying SQL engine to list all CustAccount entries starting with '123' (= filter string '123*') allows using an index on CustAccount to quickly skip to the relevant data.

Using a wildcard at the start

Imagine that you still have that dictionnary and have to list all the words ending with 'ing'. You would have no other choice than going through the entire dictionnary and checking the ending of every word (due to the alphabetical sorting).

This explains why asking the SQL engine to list all CustAccount entries ending with '123' (= filter string '*123') means that all CustAccount values must be investigated. So the AOS loops through all the entries and uses an SQL cursor to do this. That is the FETCH_API_CURSOR statement you see on the SQL level.

Possible solutions

  1. Educate your end user that using a wildcard at the beginning of a filter string will always be slow on a large table.
  2. Step up the SQL server hardware / allocated resources (faster CPU, more RAM, faster disk, ...).
  3. Create a full text index on CustAccount (not a fan of this one and performance impact should be thoroughly investigated).

Upvotes: 1

Anže Krpič
Anže Krpič

Reputation: 265

Record a trace of this execution and locate what is a bottleneck.

Upvotes: 1

Related Questions