Flowfield slow for count type on NAV5SP1 SQL Server 2008

davmac1 · 2010-04-19

I have a customer database running under NAV 5 SP1 and SQL Server 2008.
When I ran a table to analyze data, it was extremely slow to come up - about 15 minutes, then a 10 second wait to move to a new line.
The table has a number of flowfields including several that use flowfilters.
By process of elimination using a list form, I determined the slowness is caused by one field that uses count. 4 other fields with the same set of filters on the target table that use sum instead work extrememly quickly.
Is there a reason why count would be extremely slow when sum is very fast?
This is an ISV supplied table, so I have to be careful about changes to their fields and keys.

They are using 3 filters in the table filter. I tried providing another key with the matched fields first, but that did not fix the problem.
If I just run a SQL query against the corresponding vsift view, it is fast.

kine · 2010-04-19

Sum could use SIFT technology, but Count needs to count the records... ;-)

garak · 2010-04-19

How looks your flowField formula and exist there a optimal key in the table from where u calculate :?:
for example if u calculate in an Entry Table like ILE the count for an Item, and the calcformula is
Count("Item Ledger Entry" WHERE (Item No.=FIELD(Item No.))) u need in the table Item Ledger Entry a key that begins with the "Item No."
These key should not have many other fields in his structure.

so an example.

I will calulate the count of all ILE for an item. There exist some key's in the item ledger Entry table for that example.
The calcformula is: Count("Item Ledger Entry" WHERE (Item No.=FIELD(Item No.)))
So, when u run and profile the query on sql profiler, u can see following.
Index Seek with Index$xyz (xyz is the key no) with a read of < 20 so, ita good

Now, i will count the ILE with the "Item No." that is = the "External Document no." . I know it maks no sence, but it is for the example. So the calcformula is:
Count("Item Ledger Entry" WHERE (External Document No.=FIELD(Item No.)))

What we now can see on the profiler is, that the SQL Server must scan the Clusterd index :-( , because there is no key with the Externaö Document No. at the begin.
For count, NAV needs no key, but, if there a lot of datas in the table to calculate, a key should be created if the Flowfield is often used.

Regards

davmac1 · 2010-04-19

Thanks.
Both replies are very helpful.
I already tried changing the flowfield to use a key with a different field order - the original field order started with a flowfilter - the new one starts with a field. If it is counting each row, then even using a key there is probably up to 10,000 rows to count for each value.
I will try running it thru the sql profiler.
If the add-on needs this field, the only solution for my data analysis may be to look at the data with a list form that excludes the flowfield with the count type, and then make changes to any user forms/reports that run slowly.

stryk · 2010-05-14

Hi,

well, to process a COUNT query the SQL Server also actually needs sufficient indexes to perform OK. While indexing of other SELECT queries could be a pain due to the "Dynamic Cursor" issues, these COUNTs (as SUM etc.) are not bothered by this!
Hence, if you detect a "bad" COUNT query (or SUM) you could try to apply an optimized index without necessarily changing the C/AL code ...

If you would like to post the affected query (Profiler recording) here, then maybe we could come up with some index proposal.

Flowfield slow for count type on NAV5SP1 SQL Server 2008

Comments

Categories