Filter performance issue - Native Database

bbrown

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

ara3n

Have you recently optimized the table?

bbrown

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.

bbrown

Optimization is at 94.7 %

bbrown

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.

Savatage

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?

bbrown

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.