[AX 2012 R3] Computed Column as a subquery in view, performance issue
Запись от AnGor размещена 09.02.2018 в 14:35
Теги ax 2012 r3, computed column
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:
So looks the function for computed column like:
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.
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_JOURVOUCHERINVIDX] ON [dbo].[LEDGERJOURNALTRANS]
(
[PARTITION] ASC,
[DATAAREAID] ASC,
[JOURNALNUM] ASC,
[INVOICE] ASC
)
INCLUDE ( [VOUCHER]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
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.
Всего комментариев 0
