Hi, I am new to Axapta, trying to help a company that has a 300gig database getting slower by the [you fill in the blank]. We need to buy time so that they can work out a better solution to their issues. In the meantime, we noted 10 years’ worth of information in the system, and they only need (in production) data back to Jan '13. OK, what’s worse, we note 290M records in PBATableInstance. I propose to do the drastic and delete all SalesTable records prior to 2013, all SalesLine records that tie to the previous, and all PBATableInstance records that tie to matching INVENTTRANSID records deleted from SalesLines. Furthermore, I cannot see the need (for 2013/2014) to keep the PBAPREPOSTVALUE=0 records. By deleting as proposed, I can get that 290M down to less than 100M. I am trying this out in a test environment to see what the reduced record counts do for performance. Question: Assuming we do not in the least care about information (in the prod db, at any rate) relating to orders placed prior to 2013, is this plan of action going to trigger armageddon? Or will all the things we care about in 2014 (sorta) and 2015/16 (critically) going to be fine? Ideally I would love to wave my wand and remove a lot more, but these tables are the ones I am focusing on, plus a few others where I can see INVENTTRANSID as a field I can hang onto. Am, for example, deleting data from INVENTSETTLEMENT, INVENTTRANS, etc. … only for matching records to SalesLines records I am deleting. All input appreciated. Be merciful. I’ve never used Axapta before. I would rather do this more professionally, but at the moment we’re less concerned about orphans and more concerned about keeping the company afloat by keeping order entry from a stand-still.
Which version of AX, AX 2009? AX 2012, AX 2012 R2?
Believe it or not, Axapta 3.0
Well I guess that was the kiss of death…
Actually, it’s still running and it’s good. But I have no experience with AX 3.0. See if anyone else knows something to say.
Deleting old sales orders should be fine - the crucial things, such as accounting information, are stored elsewhere. Nevertheless it’s not an universal answer - some customers depends on data from historic sales orders for returns, statistics and such things.
Make sure you delete data in a way that respects delete actions and business logic (e.g. validateDelete() / delete() methods), because they maintain DB consistency. Also don’t forget to test the process on a copy before doing changing real data in production.
Thank you, Martin - agreed on your precautions. I am steeling myself for the adventure, but I had a modicum of peace as I reflected this morning. "I can’t discover or care about a missing record in PBATABLEINSTANCE if I’m not asking about the INVENTTRANSID in that record. But I cannot care about that INVENTTRANSID if it’s tied to a Sales Order I am not asking about. And this company doesn’t care about orders that were placed more than 4 years ago. So I can delete along these lines with cautious abandon. And when I’m done, there will no doubt be many orphans in the database. Unloved ones. Unwanted ones. Thankfully we’re talking data here! Sounds so bleak. Anyway, fingers crossed. We’ll be taking a first swing in a test environment.
For me it is the inter-related table requirements, which should be easier in 3.0. As you imply from above it is a chain of data, deleting it from one you should try to clean up from related tables. As Martin says sales orders are no issue, as long as reports are written from the posted transactions, this is something to consider, but only if you run reports on the data +4 years which it seems they do not care. Do it in test, test it and see if you can live with the results - I am sure you have analysed the tables and looked at appropriate ones to cleanse.
The table you reference is the user selection table for the product configurator. This then comes back to when it is called and if you need to see the configuration again. If a customer wants a repeat configuration as per the order they placed 5 years ago, after the deletion you would have to tell them it would not be possible as you would have no record (unless you have the customer related works order and the BOM - making a leap that 3.0 holds that information after the deletion as they redesigned the whole configurator in later versions!).