Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 295 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
MaintainSIFTIndex
lyot
Member Posts: 202
Hi,
When I was studying for my exam "Installation and configuration in Microsoft Dynamics NAV", I came
across this piece of disturbing lecture:
MaintainSIFTIndex
This key property determines whether SIFT structures are created (when the
property is set to Yes) or dropped (when the property is set to No) in SQL Server
to support the corresponding SumIndexField for the Microsoft Dynamics NAV
key.
SumIndexFields are created in Microsoft Dynamics NAV to support, for
example, FlowField calculations and other fast summing operations. SQL Server
can sum numeric data by scanning the table. If the SIFT structures exist for the
SumIndexFields, summing the fields is faster, especially for large sets of records.
On the other hand, modifications to the table are slower because the SIFT
structures must also be maintained.
There are situations where SumIndexFields must be created on a key to allow
FlowField calculations, but the calculations are performed infrequently or on
small sets of data. In these situations, you can disable this property to prevent
modifications to the table from being too slow.
When I read this, I deceided to look at some default keys (who use sift) on std. nav tabels.
For example Warehouse Entry.
The key "Item No.,Bin Code,Location Code,Variant Code,Unit of Measure Code,Lot No.,Serial No.,Entry Type" has this property set on no.
Now consider if I make a flowfield (on some table) that sums "Qty. (Base)" on Warehouse Entry according to Location Code, Bin Code, Item Code and Variant Code.
There's no way to set which key to use when defining a flowfield, so this is handled by SQL I presume?
What if SQL deceides to use the above key?
I guess that's a disaster for my performance?
When I was studying for my exam "Installation and configuration in Microsoft Dynamics NAV", I came
across this piece of disturbing lecture:
MaintainSIFTIndex
This key property determines whether SIFT structures are created (when the
property is set to Yes) or dropped (when the property is set to No) in SQL Server
to support the corresponding SumIndexField for the Microsoft Dynamics NAV
key.
SumIndexFields are created in Microsoft Dynamics NAV to support, for
example, FlowField calculations and other fast summing operations. SQL Server
can sum numeric data by scanning the table. If the SIFT structures exist for the
SumIndexFields, summing the fields is faster, especially for large sets of records.
On the other hand, modifications to the table are slower because the SIFT
structures must also be maintained.
There are situations where SumIndexFields must be created on a key to allow
FlowField calculations, but the calculations are performed infrequently or on
small sets of data. In these situations, you can disable this property to prevent
modifications to the table from being too slow.
When I read this, I deceided to look at some default keys (who use sift) on std. nav tabels.
For example Warehouse Entry.
The key "Item No.,Bin Code,Location Code,Variant Code,Unit of Measure Code,Lot No.,Serial No.,Entry Type" has this property set on no.
Now consider if I make a flowfield (on some table) that sums "Qty. (Base)" on Warehouse Entry according to Location Code, Bin Code, Item Code and Variant Code.
There's no way to set which key to use when defining a flowfield, so this is handled by SQL I presume?
What if SQL deceides to use the above key?
I guess that's a disaster for my performance?
0
Comments
Disregarding the "MaintainSIFTIndex" property I presume...
Above I've defined how NAV chooses which key to use. This may or may not be the same as the index used by SQL. Other factors can impact that. The property "MaintainSIFTIndex" determines whether SQL maintains the related indexed view or calculates the result on the fly. It has no impact on the key that NAV uses. But would impact the index used by SQL since the data would be coming from the base table (if ""MaintainSIFTIndex" is off) and not the indexed view.