Filtering On a Flowfield

r44712r44712 Member Posts: 40
edited 2009-09-09 in SQL Performance
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!

Comments

  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • r44712r44712 Member Posts: 40
    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.
  • strykstryk Member Posts: 645
    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) ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • vicky_dadavicky_dada Member Posts: 106
    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..........
  • BeliasBelias Member Posts: 2,998
    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.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • vicky_dadavicky_dada Member Posts: 106
    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
  • BeliasBelias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • vicky_dadavicky_dada Member Posts: 106
    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.
Sign In or Register to comment.