Wow. It's a bit difficult to trace this way on the live system since the code could be anywhere in the app. It's possible that a query is not using the fields in the order of the index and since the kernel automatically adds the DataAreaId to the WHERE clause you do not need to specify it in code.
Varden Morris Varalth Solutions Inc. 693 Abbottsfield Road NW Edmonton, Alberta T5W 4R4 Canada (780) 752-0406 www.varalth.com CONFIDENTIALITY NOTICE This message and any attachments are confidential. If you have received this transmission in error, please be advised that any disclosure, copying, distribution or action taken in reliance upon this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. ________________________________ From: Agus Riyadi <agusa...@gmail.com> To: Axapta-Knowledge-Village@yahoogroups.com Sent: Wednesday, March 4, 2009 9:22:32 PM Subject: Re: [Axapta-Knowledge-Village] Create index with first column not dataareaid Thanks James, Yes perhaps this is not relevant to recalc/closing because there are users activity during recalc which might get captured in the profiler too, and profiler will not tell from which ax function this particular query comes from. I am just curious since DTA suggested several more indexes that have first column not dataareaid. My plan is to apply as many indexes as possible so then I can move forward to others area for improvement if performance still unacceptable. Thanks and best regards, Agus On Thu, Mar 5, 2009 at 10:46 AM, James Flavell <djf1...@gmail. com> wrote: I would double check why is recalc/closing looking at custpackingslipjour . As far as I am aware the std closing should not look at this table table so I would be a little careful...although I guess you can add the index and see but to me I would not expect any improvement in the recalc or closing ________________________________ From: Axapta-Knowledge- vill...@yahoogro ups.com [mailto:Axapta-Knowledge- vill...@yahoogro ups.com] On Behalf Of Agus Riyadi Sent: 05 March 2009 11:33 To: Axapta-Knowledge- vill...@yahoogro ups.com Subject: Re: [Axapta-Knowledge- Village] Create index with first column not dataareaid Hi Varden, Thanks for input. This comes from the profiler file during inventory recalculation process, so less likely that I will change the standard code. I will only add index from the DTA's recommendation. I think we need to concern the column order as suggested by DTA because otherwise the index will not be used and only makes additional write overhead. So we can't do anything for this kind of suggestion, can we? Just FYI, I have a client who runs recalculation for long hours (20 + hours). The only possibility is to tune the index because disk configuration (RAID etc.) will not be change in the near future. Thanks and best regards, Agus On Thu, Mar 5, 2009 at 12:04 AM, Varden Morris <vard...@yahoo. com> wrote: You are right about the DataAreaId being added to the index by the kernel. I am not sure that you need to concern yourself about the order of the fields as suggested in the backend but you need to ensure that any query coming from AX should have the fields listed in the WHERE clause in the order in which they are listed in the index. In the case below, if there is an index with the SalesId and RecId listed in that order then you should ensure that all queries sent to the database, that will possible use this index, have the fields in the WHERE clause listed in the same order. CustPackingSlipJour : salesid asc, dataareaid asc, recid asc. Varden Morris Varalth Solutions Inc. 693 Abbottsfield Road NW Edmonton, Alberta T5W 4R4 Canada (780) 752-0406 www.varalth. com CONFIDENTIALITY NOTICE This message and any attachments are confidential. If you have received this transmission in error, please be advised that any disclosure, copying, distribution or action taken in reliance upon this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. ________________________________ From: Agus Riyadi <agusa...@gmail. com> To: axapta-knowledge- village <Axapta-Knowledge- vill...@yahoogro ups.com> Sent: Wednesday, March 4, 2009 12:27:29 AM Subject: [Axapta-Knowledge- Village] Create index with first column not dataareaid Dear all, Using sql server database engine tuning advisor, I got a recommendation to add new index with the following scheme : CustPackingSlipJour : salesid asc, dataareaid asc, recid asc. Is it possible to achieve that in Ax ? As far as I know Ax always add dataareaid column as the first column of any index created from AOT. Thanks and best regards, Agus
