Sage 100

I have a customer that's upgrading from version 4.10 to 4.50. In the old version, their IM_13 file had approximately 542,000 records. Now, the IM_ItemCustomerHistoryByPeriod file has over 2.2 million records. In the old version, running the inventory sales history report took about 12 seconds to start displaying pages when searching by a product line and a customer. Now in 4.50 it's several minutes and it still spins. This is unacceptable for the customer since they use that report several times a day. I am guessing that there is nothing we can do since it's probably a Sage issue.

Your best bet is to set up a simple Access shadow database and re-driect the report. Dan B. has an relatively inexpensive and simple solution you should definately check out.

I'm doing an upgrade now: Old IM_13 = 135,960 New IM_ItemCustomerHistoryByPeriod = 235,813 Just an FYI - not sure if there's another variable increasing file sizes

Okay, well I ran the report for just one customer and one item, and at the 9 minute mark I quit. I completely see what they mean. I'm going to copy the company and purge out the data before 2008 and see how much of a difference that makes.

Wow this purge is taking a long time. It's been running for 40 minutes and only 14% done. Update: 1 hour and it's at 20% 1.5 hours and it's at 31% 2 hours and it's at 38% 2.5 hours and it's at 51% Whoo-hoo it only takes 5 hours to purge out data it appears!

Okay, well after 3 hours it finished with the item customer history file, and has moved onto the transaction history file. So the percentage on the progress bar was misleading.

A couple years ago I had a similar issue. Purging old history note also helped reduce the migration from 72 hours to 20 hours. You're on the right track..

I think I'm going to have to do that, but it's still WAY too slow. After purging through 2007 and leaving 2008 forward, it went down from 2.2 million rows to 768K rows. But the report went from 9+ minutes to run to 3.5 minutes to run. But the client is used to the report starting to come up in 12 seconds. Yes, seconds. We were starting to wait 30 seconds and they were complaining at how slow it was because it was already twice as slow.

Did the whole report come up in 12 seconds or they would just see the first page with that 12 seconds? The old (Providex) reports would display information as it was reading through and selecting records. With Crystal Reports, all records are first written to a work file, then control is given to Crystal. I would imagine that the Providex program would be faster in general as there are less steps involved for it to generate the report, but did the whole report actually generate in 12 seconds or only the first page?

I believe it was the first 3 pages that generated in 12 seconds and then it continued to generate.

Here is the sum of my analysis: 2001 forward has 2.2 million records and the report takes 9+ minutes. 2008 forward has 768K records and the report takes 3.5 minutes. 2009 forward has 555K records and the report takes 20 seconds. I don't know why there is such a discrepancy between the number of records and the time it takes to run. All these tests were done on the server directly, so it would of course be even slower on the workstation.

@DawnAnastasi - have you tried checking how long it takes to complete creating the work file in each case? If that has a delay in the 2008 or older, then maybe there is a corrupt record (or more than one) hanging things up.

Also what selection criteria is being used for this report, what is different from the defaults? It uses a SELECT statement with a generated WHERE clause to read from the file now. It might be possible to gain some improvements by looking at it from this angle.

The two parameters I used in testing all scenarios were selecting just one customer, and selecting one product line. The ""creation of work file"" seems to take the majority of the time, because once that happens it only takes a couple seconds for the Crystal Report to display.

Based on what you have said, the WHERE clause has ""1=1 AND FiscalYear$ = ""2010"" and UCS(ARDivisionNo$+CustomerNo$) = ""01ABF"""". Which means that it will read through every record tied to the selected customer and fiscal year. It will then read the item file for each record to filter by product line.

I spoke with the client based on the results of the testing and they are willing to keep just 2010 and forward for history.