AXForum  
Вернуться   AXForum > Блоги > AnGor
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Оценить эту запись

[AX 2012 R3] Computed Column as a subquery in view, performance issue

Запись от AnGor размещена 09.02.2018 в 14:35

From time to time it's easy to use computed column as a subquery in view instead of join another one table.
In this case productivity of view can be significantly increased if to the subquery will be added filter for partition and dataareaid.

Also I've created an index for LedgerJournalTrans, for 3 fields:
JournalNum
Invoice
Voucher (included)

I was wondered, how the index looks in SQL like:
PHP код:
CREATE NONCLUSTERED INDEX [I_212GOA_JOURVOUCHERINVIDXON [dbo].[LEDGERJOURNALTRANS]
(
    [
PARTITIONASC,
    [
DATAAREAIDASC,
    [
JOURNALNUMASC,
    [
INVOICEASC
)
INCLUDE (     [
VOUCHER]) WITH (PAD_INDEX OFFSTATISTICS_NORECOMPUTE OFFSORT_IN_TEMPDB OFFDROP_EXISTING OFFONLINE OFFALLOW_ROW_LOCKS ONALLOW_PAGE_LOCKS ONON [PRIMARY]
GO 
So looks the function for computed column like:
X++:
server public static str getLedgerVoucher()
{
    return strFmt(@"
            (SELECT TOP 1 Voucher FROM LedgerJournalTrans where
             PARTITION = %1
            AND DATAAREAID = %2
            AND JournalNum = %3
            AND Invoice = %4)"
            ,SysComputedColumn::returnField(
            tableStr(GOA_InInvoicesContainerTrOrderVW),
            identifierStr(FlxUs_LinerPayableInvoiceTable_1),
            fieldStr(FlxUs_LinerPayableInvoiceTable, PARTITION))
            ,SysComputedColumn::returnField(
            tableStr(GOA_InInvoicesContainerTrOrderVW),
            identifierStr(FlxUs_LinerPayableInvoiceTable_1),
            fieldStr(FlxUs_LinerPayableInvoiceTable, DATAAREAID))
        ,SysComputedColumn::returnField(
            tableStr(GOA_InInvoicesContainerTrOrderVW),
            identifierStr(FlxUs_LinerPayableInvoiceTable_1),
            fieldStr(FlxUs_LinerPayableInvoiceTable, LedgerJournalId))
        ,SysComputedColumn::returnField(
            tableStr(GOA_InInvoicesContainerTrOrderVW),
            identifierStr(FlxUs_LinerPayableInvoiceLine_1),
            fieldStr(FlxUs_LinerPayableInvoiceLine, LinerPayableInvoiceId))        );
}


The statistics for query select * from GOA_ININVOICESCONTAINERTRORDERVW without additional filter:
(1993 row(s) affected)
Table 'Worktable'. Scan count 1993, logical reads 10989919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LEDGERJOURNALTRANS'. Scan count 1, logical reads 31765, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FLXUS_LINERPAYABLEINVOICELINE'. Scan count 1, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FLXUS_LINERPAYABLEINVOICETABLE'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 13993 ms, elapsed time = 14202 ms.

..and with filter:
(1993 row(s) affected)
Table 'LEDGERJOURNALTRANS'. Scan count 1993, logical reads 8514, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FLXUS_LINERPAYABLEINVOICELINE'. Scan count 1, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FLXUS_LINERPAYABLEINVOICETABLE'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 63 ms.
Размещено в Без категории
Просмотров 173 Комментарии 0
Всего комментариев 0

Комментарии

 


Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 01:33.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.