Flowfield slow for count type on NAV5SP1 SQL Server 2008

davmac1

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

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

garak

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

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

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.