Отмена закрытия склада. Оптимизация.

[vallys]

Здравствуйте!
На форуме подобных исправлений не нашел.

Имеем 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.
В результате отмена идет минимум в два раза быстрее самого закрытия (специально не замерял).

Выше упомянутые измененные методы

/// <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);
        }
    }
}

/// <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 Оптимизация запросов еще не потеряла свою актуальность.