I find that the impact of the datastructure choosen to implement Inventdimensions is too heavy performance wise and I would really like to see MS - Ax Team in the future revise this structure.
In the product developped before called XAL the inventory dimension fields were present in the tables concerned that is we had the Warehouse as a field on the equivalent to the SalesLine, CustInvoiceTrans, InventTrans etc etc.
Finally this approach allowed for really huge datastructures with simple index based group by type queries and simple index constructs. Compared to the forced joins we have in our current inventsum sphagetti code especially with the huge number of inventdim records when using Batch or Serial numbers.
What would I like to see :
1. Delete Inventdim as a table (whew what a performance boon that would be)
2. Create an Array field like the finance dimensions Called InventSKU with three fields Config, Size and Colour
3. Create an Array field InventStorage with three fields Warehouse, Location, Pallet
( add a fourth one called Site for version 5 compatibility )
4. Create an Array field InventCondition with three fields Batch, SerialNumber, and Condition
I only introduced one new field in the above as compared to V5 and two new fields V3 and V4 (V5 Adds one)
5. Add the above array fields to all tables containing inventdimID, I realise that this means 2 times the fields for the transfer table and will cause a lot of code changes :-)
However the performance improvement in large scale installations !!!!
Also the ease of adding new Inventory dimensions !!!!
The simplification of inventsum logic !!!!
Take Care
Sven
Subscribe to:
Post Comments (Atom)
3 comments:
>> Create an Array field InventCondition with three fields Batch, SerialNumber, and Condition
Do you plan to make this fields integer type, not string like in Ax3.0 ?
I think it can minimize size of indexes. Besides, DB perform operations with numbers much faster than with strings.
I believe that it would be a hughe performance gain but also a hughe functional loss to only have numbers in all the references.
Plus converting existing data will be an even bigger job :-(
Best Regards
Sven
I understand what you're getting at, the performance problems in Ax due to InventDim is a big problem. However, I believe that your solution is only solving the symptoms rather than the real problem. Focus on making the database abstraction layer 100 percent compatible with SQL and relation databases, and the problem goes away the right way. Because you could then create views containing exactly what you need, you'd get rid of inconsistancies in the data by constraints. You'd also be able to write T-SQL or PL/SQL to retrieve data "faster".
Just my two cents.
/ Tobias Nystrom, Gangsta Software
Post a Comment