I'm working on an AX 2009 installation. The job is to update the WMSOrderTrans table. Here is what I have got so far:
WMSOrderTrans wmsOrderTrans;
;
while select wmsOrderTrans
{
if (wmsOrderTrans.BBBpackingSlipExists())
{
ttsBegin;
wmsOrderTrans.selectForUpdate(true);
wmsOrderTrans.BBBPackingSlipExists = NoYes::Yes;
wmsOrderTrans.doUpdate();
ttsCommit;
}
}
The job takes about an hour to finish on the test system. This makes me worry about the performance on the production system.
At the moment the code has been written like this to have minimal locking issues (selectForUpdate is done for each row if it should be updated and is then immediatly committed). The reason is, that users will be working on the system, when the job is running.
My question is, if there is a reasonable way to implement this job in a way with less transaction overhead.
while select forUpdate ...
... does not seem to be an option, because it would lock the table until the job is finished.
Any input is appreciated.
This is the code for the BBBPackingSlipExists method:
display boolean BBBpackingSlipExists()
{
InventDim inventDimCur;
InventDim inventDimPackSlip;
InventTrans inventTransPackSlip;
;
select firstonly RecId from inventTransPackSlip
where inventTransPackSlip.InventTransId == this.inventTransId
&& (inventTransPackSlip.StatusIssue == StatusIssue::Deducted
|| inventTransPackSlip.StatusIssue == StatusIssue::Sold)
&& !inventTransPackSlip.PackingSlipReturned
exists join inventDimCur
where inventDimCur.inventDimId == this.inventDimId
exists join inventDimPackSlip
where inventDimPackSlip.inventDimId == inventTransPackSlip.inventDimId
&& inventDimCur.inventSerialId == inventDimPackSlip.inventSerialId
;
if (inventTransPackSlip.RecId != 0 && this.isReserved)
{
return true;
}
return false;
}
This looks like a prime candidate to convert to set based logic, I'd go for something like this. Please note that the job isn't tested at all since I don't have a 2009 environment handy (this doesn't even compile on 2012) so if you need to change the code feel free to edit it into my answer.
Note that the isreserved check is built into the query as well as the exists joins from the packingslipexists method
static void Job250(Args _args)
{
WMSOrderTrans wmsOrderTrans;
InventDim inventDimCur;
InventDim inventDimPackSlip;
InventTrans inventTransPackSlip;
;
wmsOrderTrans.skipDatabaseLog(true);
wmsOrderTrans.skipDataMethods(true);
wmsOrderTrans.skipEvents(true);
update_recordset wmsOrderTrans setting BBBPackingSlipExists = NoYes::Yes
where wmsOrderTrans.isReserved
exists join inventTransPackSlip
where inventTransPackSlip.InventTransId == wmsOrderTrans.inventTransId
&& (inventTransPackSlip.StatusIssue == StatusIssue::Deducted
|| inventTransPackSlip.StatusIssue == StatusIssue::Sold)
&& !inventTransPackSlip.PackingSlipReturned
exists join inventDimCur
where inventDimCur.inventDimId == wmsOrderTrans.inventDimId
exists join inventDimPackSlip
where inventDimPackSlip.inventDimId == inventTransPackSlip.inventDimId
&& inventDimCur.inventSerialId == inventDimPackSlip.inventSerialId;
}
See the documentation on update_recordset and why the skip* methods might be necessary
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With