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

 

  

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

Bookmark and Share

Comments

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

I share the same concerns, especially for customers that have HUGE numbers of entries in tables such as the Value Entry. In some of the databases I've worked with, there are hundreds of millions or records in those tables.

Not only would the VSIFT slow users down, but effectively makes it so that these end users won't be able to upgrade any longer.

Very troubling indeed. What would really solve this is the option to choose between SIFT and VSIFT, either on a table design level or database wide.

Monday, June 02, 2008 2:34 PM by DenSter

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

When I tested this myself (with the ALFA), my concern was about the reading speed, and in fact, when I got lots of records the speed went down a lot.

For flowfields, I could fix it by creating a new index (preferably BEFORE the correct one) with ONLY the fields needed for it (e.g. without "Posting Date") and NOT maintain the SQLIndex. When I don't filter on "Posting Date", Navision takes that index. The problem is when the programmer has written the "SETCURRENTKEY" to be used to do a CALCSUMS...

Tuesday, June 03, 2008 2:57 PM by kriki

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

The solution is really simple. (IMHO).

Put back the SIFT levels for the VSIFTS.

Covering indexes will be dropped by the next tablechange.

In fact the SCHEMABINDING causes SP1 to be a nightmare in large DEV databases. (And I like large DEV databases).

Tuesday, June 03, 2008 10:35 PM by Marq

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

[quote]Covering indexes will be dropped by the next tablechange.[/quote]

You don't even have to change the table, a simple re-compilation (F11) will drop and re-create the VSIFT, the "Covering Index" vanishes and has to be rebuild manually.

Tuning is not easier with 5.0 SP1 ...

Wednesday, June 04, 2008 8:36 AM by Jörg A. Stryk

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

this happened on SQL 2000 or SQL 2005 ?

Wednesday, June 04, 2008 4:42 PM by paolo titta

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

This test was performed with SQL Server 2005 (Standard Edition, Build 3200)

Since NAV 5.0 SP1 is optimized for SQL 2005 you should not run it with SQL 2000 anyway ...

Wednesday, June 04, 2008 5:13 PM by Jörg A. Stryk

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

The indexed view maintenance on SQL2000 is not perfect, it might generate a tablescan.

I just read some article about it, but I can't find it back...

But the base is : don't use SQL2000 with NAV5.0 SP1!

Wednesday, June 04, 2008 5:57 PM by kriki

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

Thursday, June 05, 2008 9:18 AM by Jörg A. Stryk

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

Thursday, June 05, 2008 9:18 AM by kriki

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

;c) (Almost) Perfect timing ;c)

Thursday, June 05, 2008 9:49 AM by Jörg A. Stryk

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

Well, better then this. We replied the same link at the same minute!

Thursday, June 05, 2008 12:04 PM by kriki

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

I guess the question I have is: while the read performance degrades somewhat isn't the writing performance increase still worth it? I mean I can tell an user to sit and wait for a report to finish but when they all try to process stuff and get locked (in non SP1) is a lot harder to have that discussion.

To me it would be worth the trade-off. Except indirectly there is a lot of "reading" happening in processing also so if you get slowed there then you are back to square 1.

Any thoughts?

Thursday, June 05, 2008 5:48 PM by cnicola

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

[quote]This test was performed with SQL Server 2005 (Standard Edition, Build 3200)[/quote]

Funny thing.. I thought that additional indexes on views are picked by query optimizer only on Enterprise Edition. According to MS documentation your second covering index on view should not work... Or am I missing something ?

Wednesday, June 18, 2008 1:08 AM by sguzek

# re: „Indexed Views“ performing slower than „SIFT Tables“ ?

Hi Jôrg

Nice work, very informative.

Yes, reading might be slow with index view.

But I guess the idea is that "less" writing and keys put less load on the server and this free up ressource on the server. Making it overall faster as the total workload is less.

BR Per

Wednesday, June 25, 2008 10:39 PM by Per Juhl