Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
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!)
INDEX ssi_CovIdx ON "xxx$380$1"
No. of Recs (SIFT): 223.080
The original query:
(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
VIEW [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]
"Initial Entry Due Date",
"Posting Date","Currency Code",
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"
BY "Vendor No_","Initial Entry Due Date","Posting Date","Currency Code"
-- Clustered Index on View
INDEX [VSIFTIDX] ON [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]
[Initial Entry Due Date],
No. of Recs (VSIFT): 223.138
Corresponding query on VSIFT:
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]
Reads: 53 pages
CPU: 31 milliseconds
Duration: 36 milliseconds
Execution Plan: Clustered Index Seek on VSIFTIDX
Additional Index: Covering Index on VSIFT (Tuning!)
INDEX ssi_CovIdx ON "xxx$Detailed Vendor Ledg_ Entry$VSIFT$3"
"SUM$Debit Amount (LCY)",
"SUM$Credit Amount (LCY)"
Reads: 10 pages
CPU: 16 milliseconds (?)
Duration: 9 milliseconds
Index Seek (Cov. Idx)
Index Seek (Clustered 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!
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.
[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 ?
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.
Well, better then this. We replied the same link at the same minute!
;c) (Almost) Perfect timing ;c)
Found it : blogs.msdn.com/.../microsoft-dynamics-nav-5-0-sp1-and-sql-server-2000.aspx
Here is also some info:
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!
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 ...
this happened on SQL 2000 or SQL 2005 ?
[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 ...
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).
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...
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.