Inventtrans cleanup

(0) Share

Report

Posted on by Neilb22

Hi all

Our AX 2012 R3 system has been running for about 3 years. The Inventtrans table has grown immensely . I have ran the on-hand entries cleanup but it does not really make a difference.

Is there any other way to shrink the rows in this table (it is sitting over 9mil)

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Do you experience some issues because of the size of the table?

9 million doesn't sound huge.

Are you planning to upgrade to D365 in the coming years? If yes, you could just leave old transactions behind in the upgrade, and migrate only master data and opening balances to D365.

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

Hi Neilb22,

The inventtrans table is a table that you should almost never touch because a lot of things depend on those transactions.

Unless you see a huge performance issue, I would recommend not trying to do any kind of cleaning, correction or something else because it is one of the main / central tables in AX.

Best regards,

Ludwig

Was this reply helpful? Yes No
André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

Like (1)

Report

Hi Neilb22,

A small additional comment to the important notes already given above. The Inventory transactions are referenced throughout the system. When you perform a consistency check, it will verify the InventTrans table with the InventSum table. When you delete records, you cant use the consistency check anymore and an option to repair the InventSum table in case of emergency is lost.

Was this reply helpful? Yes No
Neilb22 42 on at

Like (0)

Report

Yes we are experiencing some performance issues on some SQL reports linked to the inventtrans table. The 'inner join' is processing to many lines and it causes some reports to run over an hour

Was this reply helpful? Yes No
Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Neilb22,

Are the reports that face those performance issues standard reports or customized ones?

If they are customized ones, you might probably be able to check and 'tune' the way how they extract the data.

Alternatively, you can use BI tools for analyzing those inventory data.

Best regards,

Ludwig

Was this reply helpful? Yes No
Neilb22 42 on at

Like (0)

Report

Hi Ludwig , it is custom reports. I believe you are right I need to tune the query or try and run it through BI

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

I also agree that you need to take a look at your reports, queries and indexes.

If your report is slow, the first approach should not be to delete data from the system :)

Was this reply helpful? Yes No
TheGoodDBA 235 on at

Like (1)

Report

Here is a checklist for poor performing reports in AX:

Start by analyzing the query:

1. Do the joins in the query make sense?

2. Are all of the table in the query resulting in index hits when executed, or are there table scans?

3. Are there missing indexes requested by the index advisor? If so, evaluate the difference.

Are the reports written in best practice?

1. Query returns filtered results, not using the report to filter the results, when avoidable.

2. Should the report be converted to RDP if query is expensive?

These should send you down the right path. Chances are high the 9 million rows in InventTrans is not your performance problem.

Was this reply helpful? Yes No