Здравствуйте!
На форуме подобных исправлений не нашел.
Имеем AX2009 RU5 + MS SQL 2008 R2
Отмена склада идет раз в 10 дольше, чем само закрытие.
Причина: неумелое использование index hint.
Подробности:
В методах \Classes\InventCostClosingCancel_WorkInvent\reverseInventoryAdjustments() и \Classes\InventCostClosingCancel_WorkInvent\duplicateSettlements() в запросах используется index hint DateVoucherIdx, что приводит к
Key Lookup (он же Clustered Index Seek с LOOKUP). Данный индекс состоит из полей TransDate, Voucher, RecId. Но в запросах также учавствуют условия по таким полям, как ItemId, InventTransCurrency_RU, Cancelled, SettleModel. Для таких условий целесообразней использовать (из существующих) индекс ItemDateIdx.
Решение:
1. В методах \Classes\InventCostClosingCancel_WorkInvent\reverseInventoryAdjustments() и \Classes\InventCostClosingCancel_WorkInvent\duplicateSettlements() убрать index hint DateVoucherIdx (или поменять на «более подходящий» индекс).
2. В качестве оптимизации, можно добавить в индекс ItemDateIdx поля Voucher, InventTransCurrency_RU, Cancelled, SettleModel, TransRecId.
В результате отмена идет минимум в два раза быстрее самого закрытия (специально не замерял).
Выше упомянутые измененные методы
X++:
/// <summary>
/// Reverses the inventory adjustemts on the inventTrans records made during an inventory closing..
/// </summary>
protected void reverseInventoryAdjustments()
{
InventTrans inventTrans;
InventSettlement inventSettlement;
Map inventTransMap;
;
inventTransMap = new Map(Types::Int64, Types::Record);
// First of all, select all the inventTrans records that are necessary
while select forupdate * from inventTrans
where inventTrans.ItemId == itemId
join TableId from inventSettlement
where inventSettlement.TransRecId == inventTrans.RecId
&& inventSettlement.ItemId == itemId
&& inventSettlement.Voucher == cancelClosing.Voucher
&& inventSettlement.TransDate == cancelClosing.TransDate
// <GEEU>
&& inventSettlement.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& inventSettlement.Cancelled == NoYes::No
&& inventSettlement.TransRecId != 0
&& (inventSettlement.SettleModel != InventSettleModel::PhysicalValue
|| (inventSettlement.SettleModel == InventSettleModel::PhysicalValue
&& inventTrans.StatusIssue == StatusIssue::Deducted))
{
inventTransMap.insert(inventTrans.RecId, inventTrans);
}
while select sum(QtySettled),sum(CostAmountSettled),sum(CostAmountAdjustment) from inventSettlement
// VALY, 22.07.2011 -->
// ускоряем отмену закрытия склада
// закоментирован код:
// index hint DateVoucherIdx
// VALY, 22.07.2011 <--
group by TransRecId
where inventSettlement.ItemId == itemId
&& inventSettlement.Voucher == cancelClosing.Voucher
&& inventSettlement.TransDate == cancelClosing.TransDate
// <GEEU>
&& inventSettlement.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& inventSettlement.Cancelled == NoYes::No
&& inventSettlement.TransRecId != 0
&& inventSettlement.SettleModel != InventSettleModel::PhysicalValue
{
if (inventTransMap.exists(inventSettlement.TransRecId))
{
this.updateFinancialCostAmount(inventSettlement, inventTransMap.lookup(inventSettlement.TransRecId));
}
else
{
this.updateFinancialCostAmount(inventSettlement);
}
}
while select forceplaceholders sum(CostAmountAdjustment) from inventSettlement
// VALY, 22.07.2011 -->
// ускоряем отмену закрытия склада
// закоментирован код:
// index hint DateVoucherIdx
// VALY, 22.07.2011 <--
group by TransRecId
where inventSettlement.ItemId == itemId
&& inventSettlement.Voucher == cancelClosing.Voucher
&& inventSettlement.TransDate == cancelClosing.TransDate
// <GEEU>
&& inventSettlement.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& inventSettlement.Cancelled == NoYes::No
&& inventSettlement.TransRecId != 0
&& inventSettlement.SettleModel == InventSettleModel::PhysicalValue
exists join inventTrans
index hint RecId
where inventTrans.RecId == inventSettlement.TransRecId
&& inventTrans.StatusIssue == StatusIssue::Deducted
{
if (inventTransMap.exists(inventSettlement.TransRecId))
{
this.updatePhysicalCostAmount(inventSettlement, inventTransMap.lookup(inventSettlement.TransRecId));
}
else
{
this.updatePhysicalCostAmount(inventSettlement);
}
}
}
X++:
/// <summary>
/// Duplicates the inventsettlement records and marks them as canceled.
/// </summary>
protected void duplicateSettlements()
{
InventSettlement cancelSettlenent;
InventSettlement inventSettlement;
InventTrans inventTrans;
Voucher newVoucher = inventClosing.Voucher;
TransDate newTransDate = inventClosing.TransDate;
NoYes notPosted = NoYes::No;
NoYes notCanceled = NoYes::No;
#LOCALMACRO.InventSettlementFieldsChanged
TransRecId,
InventTransId,
ItemId,
newTransDate, // New TransDate
newVoucher, // New Voucher
SettleTransId,
QtySettled,
CostAmountSettled,
CostAmountAdjustment,
BalanceSheetAccount,
OperationsAccount,
notCanceled, // Canceled = No
SettleModel,
Dimension,
BalanceSheetPosting,
OperationsPosting,
ItemGroupId,
notPosted, // Posted = No
SettleType
// <GEEU>
,
inventTransCurrency,
MarkupCode_RU
// </GEEU>
#ENDMACRO
;
// First of all, duplicate all the settlements not type physical value
inventSettlement.skipDataMethods(true);
insert_recordset inventSettlement
(
#InventSettlementFields
)
select
#InventSettlementFieldsChanged
from cancelSettlenent
// VALY, 22.07.2011 -->
// ускоряем отмену закрытия склада
// закоментирован код:
// index hint DateVoucherIdx
// VALY, 22.07.2011 <--
where cancelSettlenent.Voucher == cancelClosing.Voucher
&& cancelSettlenent.TransDate == cancelClosing.TransDate
// <GEEU>
&& cancelSettlenent.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& cancelSettlenent.ItemId == itemId
&& cancelSettlenent.Cancelled == NoYes::No
&& cancelSettlenent.TransRecId != 0
&& cancelSettlenent.SettleModel != InventSettleModel::PhysicalValue;
// Now duplicate all the ones that are physical value and where the transaction is still
// not financially updated
// First of all, duplicate all the settlements not type physical value
inventSettlement.skipDataMethods(true);
insert_recordset inventSettlement
(
#InventSettlementFields
)
select
#InventSettlementFieldsChanged
from cancelSettlenent
// VALY, 22.07.2011 -->
// ускоряем отмену закрытия склада
// закоментирован код:
// index hint DateVoucherIdx
// VALY, 22.07.2011 <--
where cancelSettlenent.Voucher == cancelClosing.Voucher
&& cancelSettlenent.TransDate == cancelClosing.TransDate
// <GEEU>
&& cancelSettlenent.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& cancelSettlenent.ItemId == itemId
&& cancelSettlenent.Cancelled == NoYes::No
&& cancelSettlenent.TransRecId != 0
&& cancelSettlenent.SettleModel == InventSettleModel::PhysicalValue
exists join inventTrans
index hint RecId
where inventTrans.RecId == cancelSettlenent.TransRecId &&
inventTrans.StatusIssue == StatusIssue::Deducted;
// Now I can update all the records that I have created and reverse the signs
// Additionally, cancelled will be set to yes, so that this statement does not touch
// records that have already been reversed
inventSettlement.skipDataMethods(true);
update_recordset inventSettlement
setting Cancelled = NoYes::Yes,
QtySettled = -1 * inventSettlement.QtySettled,
CostAmountSettled = -1 * inventSettlement.CostAmountSettled,
CostAmountAdjustment = -1 * inventSettlement.CostAmountAdjustment
where inventSettlement.Voucher == newVoucher
&& inventSettlement.TransDate == newTransDate
// <GEEU>
&& inventSettlement.InventTransCurrency_RU == inventTransCurrency
// </GEEU>
&& inventSettlement.Cancelled == NoYes::No
&& inventSettlement.ItemId == itemId;
}
P.S. Кстати, статья Сергея Мазуркина про index hint
Оптимизация запросов еще не потеряла свою актуальность.