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

[Blog bot]

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