Index View Matching & Dynamics NAV 5.0 SP1

  • Article

In a recent question we were asked whether it was required to use Dynamics NAV 5.0 SP1 with Microsoft SQL Server 2005 Enterprise or Developer Edition to gain better performance, from the changes we have done in Dynamics NAV 5.0 SP1, and the answer is "NO". I will in this blog post try to explain why.

Indexed views on SQL Server 2005 includes functionality like "index view matching" and is a capability that enables SQL Server (Enterprise and Developer Edition only) to automatically get the result of any query from an indexed view if it deems that this is faster than going to the base table. In other words this really makes indexed views act like indexes (A faster way to retrieve data). This functionality (index view matching) is not used in Dynamics NAV 5.0 SP1.

The benefit of index view matching is seen when we create an indexed view like:
CREATE VIEW GLEntry$VSIFT1 WITH SCHEMABINDING AS SELECT AccountNo, COUNT_BIG(*) $Cnt, SUM(Amount) SUM$Amount FROM GLEntry GROUP BY AccountNo

And execute a query like:
SELECT AccountNo, SUM (Amount) FROM GLEntry WHERE AccountNo='1000'

Then SQL server automatically gets the result from the indexed view rather than the base table.

Note: The functionality described above only works on enterprise and developer editions of Microsoft SQL Server 2005. In Microsoft Dynamics NAV 5.0 SP1 we didn't choose to use it as we did not want to limit Dynamics NAV implementations to the Enterprise Edition of Microsoft SQL Server.

Instead Dynamics NAV 5.0 SP1 will get data from the view explicitly like this:
SELECT AccountNo, SUM$Amount FROM GLEntry$VSIFT1 WITH NOEXPAND WHERE AccountNo='1000' GROUP BY AccountNo

We split calcfields/calcsum calls per table and if no indexed view contains all fields then we will go to the base table.

We choose the indexed view based on a simple filter cost calculation and of course the SIFT index must be able to honor all the SumFields and all the filter fields:

Filter:
"A=? AND B=? AND C>=? AND C<=?"

Keys:
A,B,C,D  - cost = 0+0+0+(1<<15-3)  = 4096
A,B,D,C  - cost = 0+0+(1<<15-2)+0  = 8192
A,B,C    - cost = 0+0+0 = 0!
D,A,B,C  - cost = (1<<15-0)+0+0+0) = 32768
A,D,E,B,C- cost = 0+(1<<15-1)+(1<<15-2)+0+0 = 24576

Martin Nielander
Program Manager

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.