Condividi tramite

Super Speedy SQL Server SIFTs in Dynamics NAV 5.0 SP1

  • Articolo

The way Dynamics NAV manages SIFTs in version 5.0 SP1 is a radical step forward from previous versions. Dynamics NAV is now leveraging the power of native SQL Server functionality for this important feature.  Prior to SP1, ever since Dynamics NAV was first ported to SQL Server, each SIFT index was implemented as a separate SQL Server table which had to be updated by a standard SQL Server trigger every time the base data table was changed.  Building just a few SIFT indexes could slow certain Dynamics NAV operations significantly and many partners have had to become quite adept at SIFT performance tweaking.

All of this is considerably simplified in Dynamics NAV 5.0 SP1. SIFT tables are a thing of the past, SIFT triggers are no more. SIFT indexes are now implemented as “Indexed Views” which are a powerful feature of the SQL Server database engine. When you update a Dynamics NAV table which includes one or more SIFT indexes, SQL Server’s Indexed View(s) will automatically take care of maintaining the SIFT totals.

A SQL Indexed View is a materialised view which is most often used to keep aggregated totals based on a related SQL Server table. As Indexed Views are an integral part of the database engine they can maintain SIFT totals more efficiently than a structure involving custom TSQL triggers and separate tables. As a bonus, we will never have to worry about “zero SIFT records” ever again!

Part of this change means that you can no longer specify SIFT levels as all key columns are now included in each SIFT index. The “SIFTLevels” column (i.e. SIFTLevelsToMaintain property) has been removed from the Key maintenance window in Dynamics NAV 5.0 SP1.

This change, along with other performance enhancements in Dynamics NAV 5.0 SP1 (e.g. batching repeated inserts for the database into a single “Bulk Insert” operation), have delivered very significant improvements in the speed of certain typical Dynamics NAV operations.

If you would like to know more about this and some of the other significant SQL changes in Dynamics NAV 5.0 SP1 why not join the Webcast on this subject scheduled for 6/5/08. This will be one hour well spent if you have an interest in Dynamics NAV running on SQL Server.

For more details about the Webcast see: https://blogs.msdn.com/ukdynsupport/archive/2008/04/17/webcast-sql-related-changes-in-microsoft-dynamics-nav-5-0-sp1.aspx

For more information about other improvements in Dynamics NAV 5.0 SP1 see: https://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2007/11/30/overview-of-microsoft-dynamics-nav-w1-5-0-sp1.aspx

For more details about SQL Server Indexed Views see SQL Server 2005 Book online.

Best regards,

Gerard Conroy

Microsoft Dynamics NAV Support, UK.

Comments