Filter performance issue - Native Database

bbrown
bbrown Member Posts: 3,268
edited 2007-11-01 in NAV/Navision Classic Client
I have a simple list report using the "Sales Shipment Line" table only. The report is designed to except a shipment date range from the user. The "Shipment Date" is in the current key. The table contains about 5 million records.

If the date range contains records the report runs very fast.

if the report range does not contain records the system will scan the entire table and take a few minutes

Thoughts?


P.S. I see the same behavior with a list form
There are no bugs - only undocumented features.

Comments

  • ara3n
    ara3n Member Posts: 9,258
    Have you recently optimized the table?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrown
    bbrown Member Posts: 3,268
    The database was recently restored to a new server. There have not been many transactions since. This is a test system being used for development and testing purposes. I'll give an optimize a try and let you know. Can't hurt to try.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    Optimization is at 94.7 %
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    After optimizating the optimization percent remains at 94.7%. An indication that the table was fairly well optimized to start with. No change in the filter behavior.

    Thanks for the idea. Any other thoughts? Hopefully the client never run the report for a range that has no data.
    There are no bugs - only undocumented features.
  • Savatage
    Savatage Member Posts: 7,142
    bbrown wrote:
    I have a simple list report using the "Sales Shipment Line" table only. The report is designed to except a shipment date range from the user. The "Shipment Date" is in the current key. The table contains about 5 million records.

    If the date range contains records the report runs very fast.

    if the report range does not contain records the system will scan the entire table and take a few minutes

    Thoughts?
    P.S. I see the same behavior with a list form

    If the Shipment Date is a Key - How & when could it be blank?
    Shipment Date for my Sales Shipment Line table is not a key. This is a new added key?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • bbrown
    bbrown Member Posts: 3,268
    It is a new key which contain shipment date as one of its fields. The situation is not that shipment date is blank, but that a given shipment date filter range does not return any records. Example: Run report for January 1st of any year.
    There are no bugs - only undocumented features.

Categories