SIFT on SQL in 5.0 SP1

JDVyska
Member Posts: 179
Checked around, saw folks referring to the SIFT change to index views, but wasn't sure there was a good thread outlining the changes. Since I came across a blog that did just that, reposting / linking here.
From: http://blogs.msdn.com/ukdynsupport/arch ... 0-sp1.aspx
There's also a Webcast on May 6th covering this topic - link can be found on the blog page.
Thought I'd share.
Small Edit to clarify the title and that this applies to SQL SIFT.
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.
From: http://blogs.msdn.com/ukdynsupport/arch ... 0-sp1.aspx
There's also a Webcast on May 6th covering this topic - link can be found on the blog page.
Thought I'd share.
Small Edit to clarify the title and that this applies to SQL SIFT.
JEREMY VYSKA
CEO, Spare Brained Ideas, Göteborg, Sweden
New (April 2021) Getting Started with Microsoft Dynamics 365 Business Central Book Available: "Your First 20 Hours with Business Central"
CEO, Spare Brained Ideas, Göteborg, Sweden
New (April 2021) Getting Started with Microsoft Dynamics 365 Business Central Book Available: "Your First 20 Hours with Business Central"
0
Comments
-
Do you know if it is possible to use indexed views on other NAV tables e.g. I have a web-application that accesses some NAV tables, where I have created an indexed view, does the modifications from NAV updates the indexed view correctly?
There are different conditions to fullfill that indexed views work properly.
Thanks.
Ray0 -
As far as I know, if you created the Index SQL-Side, Navision is completely unaware of it.
That said, I don't know a lot about SQL Indexes (yet) - if updates to the underlying table update the Index you've created, it's possible? Hopefully someone with deeper knowledge can give you an answer. You might have to give a bit more of a "For example" to get something more concrete, though.JEREMY VYSKA
CEO, Spare Brained Ideas, Göteborg, Sweden
New (April 2021) Getting Started with Microsoft Dynamics 365 Business Central Book Available: "Your First 20 Hours with Business Central"0 -
Here you can find more information about SQL Indexed Views and how the options have to be set / what conditions have to be considered:
http://msdn.microsoft.com/en-us/library/ms191432.aspx
After the clustered index is created, any connection that tries to modify the base data for the view must also have the same option settings required to create the index. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statement that will affect the result set of the view if the connection executing the statement does not have the correct option settings.0 -
Because of schema binding (referred to in the article link) I would avoid creating custom indexed views for NAV tables.
I have used regular views for simplifying reporting and list forms (read only) and with well peforming views they work well.David Machanick
http://mibuso.com/blogs/davidmachanick/0 -
I've got one question about the indexed views:
As stated in the comparison of SQL Server 2005 only the Enterprise Edition of the server could use them for matching.
So do I have to use the Enterprise Edition if I want to use NAV 5.0 SP1?Indexed view creation is supported in all editions. Indexed view matching by the query processor is supported only in Enterprise Edition.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Thanks for your advice.0 -
No you don't. It was answered in this post after I raised a service request at microsoft... :
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/05/14/index-view-matching-dynamics-nav-5-0-sp1.aspx
Actually, I had a blog-peace ready for some time now ... . Didn't know about the UK blog, apparently. So I decided just to post it like it was
.
You can find it here and here.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions