Sage 100

We picked up a client who is running 4.4.06 (MAS 200) with a custom report using AR_InvoiceHistoryHeader, AR_InvoiceHistoryDetail and SO_ShipToAddress. When you run the report for a single day, the report finishes within 5 seconds. If you run 2 days or greater the report will take longer than an hour. All 3 tables have been rebuilt. We are trying to figure out why the disparity once you add a 2nd day to the report. Any ideas would be appreciated. I've created the report in Intelligence and it takes 2 minutes to run for the complete month but the client doesn't want to use Intelligence. (After doing a test migration to 2014) The record selection is: {AR_InvoiceHistoryHeader.InvoiceDate} in {?StartDate} to {?EndDate} and {AR_InvoiceHistoryDetail.ItemType} <> ""4"" and {@state_chk}<>0 and {AR_InvoiceHistoryHeader.ModuleCode} = ""S/O

Which table is the main table?

Does the linking start with the detail table?

The main table was the detail table but we changed it to the header table. Regards, Lloyd

This will probably not have any impact but these are the things I would try: 1) Change your range argument to >= start date and <= end date 2) Move the fourth condition to be the second condition 3) What is the formula use to calculate @state_chk? is this something that can be expressed in the select clause natively? All of the above would be an attempt to optimize the query to make it easier for Providex to process. I have found that using different operators have different levels of efficiencies. I expect this will be a matter of trial and error. I agree that using the header table as primary is a good way to go.

An update, we changed the date range argument to >= start date and <= end date, moved some of the other criteria around and got the report to run in 20 minutes from the server but it takes 1 hours and 20 minutes from a workstation. I told them to have their IT people look over the network to increase the performance of the report.