Hi,
I would like to speed up one method, and the exact method in the class inventsumdateengine \ selectinventonhand. I have no idea.AX2009.
hi jakub,
Can u be more clearer with your requirement please???
Sure,
I would like to speed up third select. Never lived to see the end of the last operation. Maybe divide this query into parts. Maybe recordsortedlist.
Hi Jakub,
i hope, if we want to speed up the execution time, we have to execute on server instead of running on the client.
Thanks,
Valeru.
Hi Jacub,
In Ax or anywhere, the thing is, if the code is less, the execution time is less. avoid select queries as much as u can…
I try to speed up this insert_recordset:
insert_recordset inventSumDateTransNew (ItemId, #InventDimFields, PostedPhysicalValue, ParmId, Sign)
select forceplaceholders ItemId, #InventDimFields from inventSumDateTrans
group by ItemId,#InventDimFields
where inventSumDateTrans.ParmId == parmId &&
inventSumDateTrans.TransType == InventSumDateType::Base
join inventTransPostingPhysical
where inventTransPostingPhysical.ItemId == inventSumDateTrans.ItemId &&
inventTransPostingPhysical.InventTransPostingType == InventTransPostingType::Physical &&
inventTransPostingPhysical.IsPosted == NoYes::Yes &&
inventTransPostingPhysical.TransDate > perDate
join sum(CostAmountPhysical), parmId /scalar/, minus /scalar/ from inventTrans
where inventTrans.VoucherPhysical == inventTransPostingPhysical.Voucher &&
inventTrans.DatePhysical == inventTransPostingPhysical.TransDate &&
inventTrans.InventTransId == inventTransPostingPhysical.InventTransId &&
inventTrans.InventDimId == inventSumDateTrans.InventDimId &&
inventTrans.CostAmountPhysical != 0;
However, with moderate effect.
Every time you have a problem with a SQL query, you should ensure yourself that indexes and statistics are in a good shape (index fragmentation, out-of-date statistics etc.), then analyze the query execution plan. Without that, you just shoot with eyes closed.
Do any of you tried to run the report InventDimPhys ? Path: Stock management > Reports > Status > Physical stock > Physical stock by stock dimension? If so, how long to generate?
I know this’s an old thread but…
What do you mean when talk about “statistics” and what do you suppose if developer doesn’t have direct access to SQL-SERVER for
analysis to Query or there is a query performance analysis tool at AX and I don’t know?
Importance of Statistics and How It Works in SQL Server
Tuning database queries without having access to database is obviously extremely difficult and reimplementing SQL tools in AX wouldn’t makes any sense. I think that requesting access to the database will be a better use of your time than looking for some other ways. Tell them they you can’t do it unless they give you tools you need.
Although you can access execution plans (for example) in AX, you need sufficient permissions in SQL Server for it. And if you get the permissions, you’ll find that doing it in SSMS makes better sense than going through your ERP system.
It´s important truncate 2 tables InventSumDateTable and if not truncate in cascate the InventSumDateTrans, delete all records. Do you know some better solution?