Filtering On a Flowfield

r44712

Hi all,

I have a requirement where a customer has an overview screen for all Sales Order Lines. There is a flowfield that has been added to the Blanket Order Subform of "Qty. on Sales Order", which they wish to see as a column on this overview.

However, and this is where it starts to fall apart, it that it is also required that they can filter on this new field. At which point performance takes a nosedive, taking forever to filter on the Sales Lines.

What can be done about this, I can't add it to a key as its a flowfield, and I'm at a loss as to how to resolve it?

I did consider creating a view based on the data they wish to see, would this help (it is a SQL installation)?

One problem with that is that there is a "outstanding cash value" for all of the filtered lines at the bottom of the screen, presumably I could just sum on the "Outstanding Value" column in the view to create the total!

Any input greatfully received!

David_Singleton

You need to explain a bit clearer exactly what this flow field is, how they are filtering on it and why?

If it is for consolidating purposes, then I suggest using some form of pre-filtering then a routine to update a new field with the required value.

But really you have not given enough information to be able to get help.

r44712

David Singleton wrote:

But really you have not given enough information to be able to get help.

I'm sorry..not sure what else there is to add...

They wish to filter on this "Quantity on Sales Order" field to determine which blanket orders are currently "active" and those which have a qty. on Sales Order of zero, to then asses if the Blanket Order Line is still required (as they have an overnight reservations process which is needlessly "holding on" to stock that will never be shipped).

The filter is applied directly to the Sales Line after the form has opened.

stryk

I recommend to record the "poor" performing process/transaction using SQL Profiler, searching for queries which take huge numbers of "Reads" (e.g. >= 1000) - this usually indicates "Index Scans" which are a primary reason for degraded perfromance.
Once you've detected the "bad query" you might be able to fix it by e.g. adding an index or other things ...

Which NAV version do you use? If it's a SIFT system (e.g. <= 4.0) you also have to look into SIFT Optimization & MAintenance (just search mibuso) ...

vicky_dada

Hi all,

Im trying to post Item reclassification Jnrl which is taking around 20secs for one user. For 2users system is giving an error " Item Ledger entry table has been locked by some other user". Earlier it was taking 2-3mins for completing the transaction. After executing all the following queries/Processes Indexing,Update Statistics,Setting the Page value to 1, setting the RAM to 9 GB for teh SQL server, REbuild Indexing, ReOrganizing in the test Server and SQL Database tuning the system has improved from 2-3mins to 20secs.

I have observed following queries which are taking more time for executing:

1. SELECT TOP 1 * FROM "SBC Tanzania Ltd$Item Ledger Entry" WITH (UPDLOCK) WHERE (("Item No_"=?)) AND (("Open"=?)) AND (("Variant Code"=?)) AND (("Positive"=?)) AND (("Location Code"=?)) ORDER BY "Item No_","Open","Variant Code","Positive","Location C

2. SELECT * FROM "SBC Tanzania Ltd$Item Application Entry" WITH (UPDLOCK) WHERE (("Inbound Item Entry No_"=?)) AND (("Item Ledger Entry No_"<>?)) AND (("Outbound Item Entry No_"<>?)) AND "Inbound Item Entry No_"=? AND "Item Ledger Entry No_"=? AND "O

can some one help in fixing these queries..........

Belias

which nav version?how big is your db?what is the C/al code which generates the statements?

ORDER BY "Item No_","Open","Variant Code","Positive","Location C

BTW it is a best practice to put boolean, option and date field as the last fields of a key (they're not very selective): in order to do this without modifying the business logic, you can deactivate the MaintainSqlIndex for this key and then recreate the same key in the right order. (do not use SQLIndex property to rearrange fields)

up to a particular nav version (i don't remember which one) this is not only a best practice, but also a performance issue.

vicky_dada

HI

we are using NAV2009, The code
IF NOT ItemLedgEntry2.FIND(EntryFindMethod) THEN
EXIT;
in codeunit 22/ApplyItemLedgEntry generates the query
SELECT TOP 1 * FROM "SBC Tanzania Ltd$Item Ledger Entry" WITH (UPDLOCK) WHERE (("Item No_"=?)) AND (("Open"=?)) AND (("Variant Code"=?)) AND (("Positive"=?)) AND (("Location Code"=?)) ORDER BY "Item No_","Open","Variant Code","Positive","Location C

The code in Item Application entry table/CheckCyclictoapplied entries generates the query
SELECT * FROM "SBC Tanzania Ltd$Item Application Entry" WITH (UPDLOCK) WHERE (("Inbound Item Entry No_"=?)) AND (("Item Ledger Entry No_"<>?)) AND (("Outbound Item Entry No_"<>?)) AND "Inbound Item Entry No_"=? AND "Item Ledger Entry No_"=? AND "O

The Database size is around 13GB, the keys in the standard function cannot be changed, since this fuction is used in many calls, and i even tried disabling the maintain SQL index and created SQL index but this has not made any diffrence.

we even tried changin the syntax of find('-') to Findfirst in cu22, but the system still takes 20 secs

Belias

vicky dada wrote:

The Database size is around 13GB, the keys in the standard function cannot be changed, since this fuction is used in many calls, and i even tried disabling the maintain SQL index and created SQL index but this has not made any diffrence.

You misunderstood me: disable the flag "maintainsqlindex" of the key: in this way, business logic is not changed because the key is already enabled
Create a new key in navision with rearranged fields.
SQL should choose your new key if you designed it correctly, and then it will "order by" your old key
(this solution should be good if you have a relatively small result set from your query -not sure of this-)
P.S.: if i am correct, nav 2009 doesn't care about the selectivity of the key: if so, don't lose more time around this :-k

vicky_dada

Hi Belias,

Thanks for the instant reply

I tried creating the new keys and rearranging the keys in all the possible ways, but the performance did not improve, is there any other way to correct the query.