Sage 100

A customer of mine recently converted from version 4.05 to version 4.40. One thing they noticed is that when you do an invoice number lookup in RMA entry, it's extremely slow. There are over 1 million records in the RMA customer invoice search history. However if you do the same task on version 4.05, it's very speedy. On version 4.40, it's dog slow and shows ""searching"" at the bottom for a long time. I was able to ""work around"" the issue by adding a global filter in the lookup for the beginning of the invoice number (from about 6 months ago; greater than ""08""). That has results start returning right away and overall it's faster than not having any filter. However, I noticed that there is no way to purge out the RMA invoice search history by date range, or to re-populate it by date range. You can either purge the whole file or repopulate the whole file. And, to add insult to injury, the table isn't available to import into via Visual Integrator! Anyone else run into this and any ways of resolving?

Hopefully, these aren't all RMA's............... 90 or 200? Probably an oversight (or more likely an under sight) by a programmer. I am sure Alnoor will have some insight on the missing index. Reminds me of migrating a client from 4.1 to 4.4 who had a gazillion NOF checks in history. Sage ultimately had to rewrite the conversion process before I could migrate the data. Actually, it took them about three months longer than Alnoor to do it.

I also have a customer who noticed overall slow behavior in RMA when they went from 4.3 to 4.4.

This is a MAS 200 system. The million records are invoice search history records... when you are in the lines tab of RMA entry you lookup by invoice number.

Is RMA set to perform warranty checkking?

No

Do they need to have all 1 mil invoices in history or can they be archived? I hate doing that (archiving) but what if you copied the live company to a test and then purged out a few years of invoice history and see if that helps? I'd also wonder if running the RMA utility to create the invoice index again would help re-index that file and speed it up. Also, when we went to level what 4.3 if a file was greater than 2 gb wasn't the file continued with something appended to the file name? If the invoice history file is split that would slow it down, I would think.

Well, they do need the invoice history (full), but your comment gave me an idea. Once they are done posting for the night, make a copy of the company. Purge out invoice history in the copy company up to 6 months ago. Then, clear out the RMA file and use the utility to re-populate it from invoice history. Back up the same data file from the live company and take the data file from the test company and put into the live company. This would need to be done every year or so to keep the speed fast, but it should work.

Sounds like it'll work. But I do wonder if you just run the RMA invoice utility if it would speed it up without having to purge.

Seems like there would be a utlilty to create an archive company. We have used the copy to test company method and that works well the first time but then as time passes and additional archives are made the client ends up with mutliple archive companies with overlapping data.

Took a look at this with some large RMA customer data on 4.30. On 4.30 on my system it took 130 secs to do the InvoiceNo lookup in RMA Entry / Lines tab. With that data converted to 4.50 it takes 60 secs. In either case it searches against AR_InvoiceHistoryHeader (ARN) as opposed to RA_CustomerInvoiceSearch (RAS) in case you were thinking of purging data out of the latter. Looking at how this ALE lookup is defined in 4.50, it does have 1 place that can use improvement but no smoking gun like a table scan instead of an index scan (which is what was happening in the AP Conversion case for @JeffSchwenk ). Changing that got me from 60 to 50 secs. Also in general customizing lookup to use less columns (e.g. removing Invoice Total gave me 5 secs) and to use filters on indexed columns (e.g. @DawnAnastasi filtering on InvoiceNo >""08"") results in a faster lookup (as expected). Then looked at how legacy lookup was defined and it is almost identical except for 1 thing and I was around when this took place. ALE was introduced in v3.50 and the very 1st slow performance bug reported was for Customer Name lookup something like that. It was fixed on v3.51 because they created a special ""memory file"" called ARNALE it's a mirror cousin of ARN just for the places where common ARN fields are part of a lookup (InvoiceNo, CustomerNo, CustomerName, BillToName, SoldToName, etc). So in the end today's InvoiceNo lookup is slower mainly because it doesn't run off an ALE mirror for AR_InvoiceHistoryHeader anymore. This would be considered a take-away from legacy.