If I produce an index in Axapta, Axapta puts automatically an additional index on the field DataAreaId. This leads to a bad performance in my form. After i delete the index for the field DataAreaId in the SQL Enterprice Manager the performance is much better (Axapta 3.0 SP2 with SQLServer2000).
Can someone help me?
Excuses my bad English !
Hi AxWorker,
I see you found the correct forum. I hope one of our AX experts on the site will help you out here.
the dataAreaId field stores the company that the record was inserted in.
Dynamics AX automatically ads this field to all indexes in all the tables, except those, that have the property “SavedataPerCompany” set to No.
This field allows to have a number of companies in the same DAX installation, Intercompany orders, etc.
I would not recommend removing this field from the index.
Thanks for the fast answer.
Those badly performance exists only when selecting older data records in the form (DataSource: ‘WMSOrderTrans’). Can you understand this?
Or did you hear of such a problem already once?
Dataareaid is automatically added as the first field in indexes. If you are not using multiple companies, you can move the DataAreaId field to be the last field in the index. This should help. The first fields in an index should be the most unique fields. If you only have one company, the DataAreaId field will always be the same (never unique) and should be at the end of the index instead of the beginning. Manually add DataAreaId as a field in your index in the table in the AOT and then move it to the end of the list of fields. Deleting it from the AOT will not make it stop using it in SQL. You just have to move it to the end.