„Indexed Views“ performing slower than „SIFT Tables“ ?

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"

(

[bucket],

[f9],

[f20],

[f4],

[s7],

[s8],

[s16],

[s17],

[s18],

[s19]

)

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):

-- Indexed View to replace SIFT Table

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"

-- Clustered Index on View

CREATE UNIQUE CLUSTERED INDEX [VSIFTIDX] ON [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]

(

[Vendor No_],

[Initial Entry Due Date],

[Posting Date],

[Currency Code]

)

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"

(

"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)"

)

Reads:    10 pages

CPU:    16 milliseconds (?)

Duration:    9 milliseconds

Execution Plan:    Index Seek on Covering Index

Results:

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 would really appreciate to get your comments and especially experiences with this issue!