Today I just ran a brief test to compare the performance of queries on "
SIFT Tables" (older NAV versions) with queries on "
Indexed Views" (introduced with NAV 5.00 SP1) called
VSIFT.
Well, in previous BLOGs or forum threads I already stated my concerns about "
reading performance" on large tables with VSIFT (no question about "
writing performance", this is definitely improved). As a "View" is just a pre-defined SELECT statement on a table (here supported by its own Index) this "View" is actually always gathering the data from the source table, e.g. the Ledger Entry table. With "old" SIFT Tables the required data is read from aggregated/summed records in dedicated tables, thus the data volume is compressed here.
Of course, there is a remarkable difference between standard/non-tuned SIFT Tables and VSIFT, but I wanted to compare
optimized SIFT with VSIFT (that's the different to common MS test-scenarios ;c) ).
So please find here the tests I ran on a customer's (test-)system:
Table (T380): Detailed Vendor Ledg. Entry
No. of Recs (T380): 1.460.800
Key: Vendor No., Initial Entry Due Date, Posting Date, Currency Code
SumIndexFields: Amount, Amount (LCY), Debit Amount, Credit Amount, Debit Amount (LCY), Credit Amount (LCY)
Buckets: Available 0 to 9,
only 7 is enabled (Tuning!)
Additional Index:
Covering Index on related SIFT Table "
xxx$380$1"
(Tuning!)
CREATE
INDEX ssi_CovIdx ON "xxx$380$1"
[color="grayfontfamilyCourier Newfontsize9pt"](
[/color]
[bucket],
[f9],
[f20],
[f4],
[s7],
[s8],
[s16],
[s17],
[s18],
[s19]
[color="grayfontfamilyCourier New"])[/color]
No. of Recs (SIFT): 223.080
The original query:
SELECT
SUM("s7"),SUM("s8"),SUM("s16"),SUM("s17"),SUM("s18"),SUM("s19")
FROM "xxx$380$1"
WITH
(READUNCOMMITTED)
WHERE
(bucket=7 AND f9='004792')
Reads: 13 pages
CPU: 0 milliseconds
Duration: 1 millisecond
Execution Plan: Index Seek on Covering Index
Indexed View (VSIFT):
[color="greenfontfamilyCourier Newfontsize9pt"]-- Indexed View to replace SIFT Table
[/color]
CREATE
VIEW [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]
WITH
SCHEMABINDING
AS
SELECT
"Vendor No_",
"Initial Entry Due Date",
"Posting Date","Currency Code",
COUNT_BIG(*) "$Cnt",
SUM("Amount") "SUM$Amount",
SUM("Amount (LCY)") "SUM$Amount (LCY)",
SUM("Debit Amount") "SUM$Debit Amount",
SUM("Credit Amount") "SUM$Credit Amount",
SUM("Debit Amount (LCY)") "SUM$Debit Amount (LCY)",
SUM("Credit Amount (LCY)") "SUM$Credit Amount (LCY)"
FROM dbo."xxx$Detailed Vendor Ledg_ Entry"
GROUP
BY "Vendor No_","Initial Entry Due Date","Posting Date","Currency Code"
[color="greenfontfamilyCourier Newfontsize9pt"]-- Clustered Index on View
[/color]
CREATE
UNIQUE
CLUSTERED
INDEX [VSIFTIDX] ON [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]
[color="grayfontfamilyCourier Newfontsize9pt"](
[/color]
[Vendor No_],
[Initial Entry Due Date],
[Posting Date],
[Currency Code]
[color="grayfontfamilyCourier New"])[/color]
No. of Recs (VSIFT): 223.138
Corresponding query on VSIFT:
SELECT
SUM("SUM$Amount"),SUM("SUM$Amount (LCY)"),SUM("SUM$Debit Amount"),SUM("SUM$Debit Amount (LCY)"),SUM("SUM$Credit Amount"),SUM("SUM$Credit Amount (LCY)")
FROM [xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]
WITH
(READUNCOMMITTED)
WHERE
("Vendor No_"='004792')
Reads: 53 pages
CPU: 31 milliseconds
Duration: 36 milliseconds
Execution Plan: Clustered Index Seek on VSIFTIDX
Additional Index:
Covering Index on VSIFT
(Tuning!)
CREATE
INDEX ssi_CovIdx ON "xxx$Detailed Vendor Ledg_ Entry$VSIFT$3"
[color="grayfontfamilyCourier Newfontsize9pt"](
[/color]
"Vendor No_",
"Initial Entry Due Date",
"Posting Date","Currency Code",
"SUM$Amount",
"SUM$Amount (LCY)",
"SUM$Debit Amount",
"SUM$Credit Amount",
"SUM$Debit Amount (LCY)",
"SUM$Credit Amount (LCY)"
[color="grayfontfamilyCourier Newfontsize10pt"])[/color]
Reads: 10 pages
CPU: 16 milliseconds (?)
Duration: 9 milliseconds
Execution Plan: Index Seek on Covering Index
Results:
SIFT
VSIFT (standard)
VSIFT (tuned)
Reads
13
53
10
CPU
0 msec
31 msec
0 msec
Duration
1 msec
36 msec
5 msec
Execution Plan
Index Seek (Cov. Idx)
Index Seek (Clustered Idx)
Index Seek (Cov. Idx)
Of course all queries delivered identical results. But reading from VSIFT took 40
more Page Reads (
about 4 times more!) and 35 milliseconds
longer (
about 35 times longer). And this table T380 is actually a
small one, in this table we have Ledger Entry tables containing far more records, e.g.
G/L Entry (23.082.836) or
Warehouse Entry (46.721.678)!
After little tuning the VSIFT by also adding a "
Covering Index" the difference the results are almost the same, VSIFT is reading less pages (- 23%) but taking little longer.
(Just to point out: the figures show the
objective measurement; the
subjective user-experience will not feel any difference here – depending on the
table size and number of queries executed within a process!)
Well, this test for sure isn't representative, but I feel my concerns about "
VSIFT performing worse than SIFT in reading transactions" are somewhat confirmed – at least VSIFT "out-of-the-box" - … and obviously still some
tuning is required to optimize the performance!
I really appreciate to get your comments and especially experiences with this issue!
Подробнее...
http://dynamicsuser.net/blogs/stryk/archiv...ift-tables.aspx