SIFT(SumIndexFields) vs NCCI (Nonclustered Columnstore Indexes)

• SIFT (SumIndexFieldTable):
  • SIFT indexes are used for aggregating data in Microsoft Dynamics 365 Business Central.
  • They are particularly useful for improving the performance of queries involving aggregate functions like SUM, AVG, COUNT, etc., especially over large datasets.
  • SIFT indexes are typically created on fields that are frequently used in aggregate calculations, such as sales amounts or quantities.
  • These indexes help accelerate queries that involve summarizing or aggregating data, such as generating reports or calculating totals
• NCCI (Non-clustered Columnstore Indexes):
  • NCCI, on the other hand, are columnstore indexes that store data in a columnar format optimized for analytical workloads.
  • They are effective for accelerating queries involving large volumes of data, especially for reporting, data warehousing, and business intelligence purposes.
  • NCCI are well-suited for aggregate queries and analytical workloads that involve querying and analyzing large datasets.
  • These indexes compress and store data in a columnar format, making them efficient for aggregate operations like SUM, AVG, etc., over large datasets.

In summary, while both SIFT and NCCI can improve query performance in Microsoft Dynamics 365 Business Central, they serve different purposes: SIFT indexes are for aggregating data and improving the performance of aggregate queries, while NCCI are for optimizing analytical workloads involving large volumes of data by storing data in a columnar format.

When to use and not to use NCCI (Nonclustered Columnstore Indexes)

• Use Non-clustered Columnstore Indexes When:
  • Analytical Workloads: Non-clustered columnstore indexes are particularly effective for analytical workloads that involve querying and analyzing large volumes of data, especially in scenarios like reporting, data warehousing, and business intelligence.
  • Aggregate Queries: If your queries involve aggregate functions (e.g., SUM, AVG, COUNT) over large datasets, non-clustered columnstore indexes can significantly improve performance by compressing and storing data in a columnar format optimized for such operations.
  • Data Warehousing: In environments where historical data is regularly queried for reporting and analysis, non-clustered columnstore indexes can accelerate queries on large fact tables commonly found in data warehousing solutions.
• Avoid Non-clustered Columnstore Indexes When:
  • Transactional Workloads: For OLTP (Online Transaction Processing) workloads characterized by frequent inserts, updates, and deletes, non-clustered columnstore indexes may introduce overhead due to the overhead of maintaining the columnstore index during these operations.
  • Small Tables: In tables with a small number of rows or where the data is relatively static, the benefits of columnstore indexes may not justify the overhead of creating and maintaining them. In such cases, traditional row-based indexes may be more suitable.
  • Real-time Data Access: If your application requires real-time access to individual records and immediate transactional consistency, non-clustered columnstore indexes may not be the best choice due to potential latency in updates caused by the batch-oriented nature of columnstore processing.

In summary, non-clustered columnstore indexes are beneficial for analytical workloads, especially in scenarios involving large volumes of data and aggregate queries. However, they may not be suitable for all use cases, particularly those involving transactional workloads with frequent data modifications or small datasets. It’s essential to carefully evaluate your application’s requirements and workload characteristics before deciding whether to use non-clustered columnstore indexes in Microsoft Dynamics 365 Business Central.

Practical example when to use NCCI over SIFT and vice versa

When to Use NCCI over SIFT:
Scenario: Analyzing Sales Data

• Requirement: You need to analyze sales data from your Microsoft Dynamics 365 Business Central system to identify trends, patterns, and sales performance over time.
• Query: You frequently run complex analytical queries involving large volumes of sales data, such as calculating total sales amounts per product category or region over multiple years.
• Optimal Choice: In this scenario, using NCCI would be ideal. NCCI is well-suited for analytical workloads involving large datasets and aggregate queries. By creating NCCI on key columns like Sales Amount and Sales Quantity, you can significantly improve the performance of these analytical queries

When to Use SIFT over NCCI:
Scenario: Generating Monthly Sales Reports

• Requirement: Your company needs to generate monthly sales reports to analyze sales performance and track progress against targets.
• Query: The monthly sales reports involve summarizing sales data, such as calculating total sales amounts and quantities per month for each product category.
• Optimal Choice: In this scenario, using SIFT would be more appropriate. SIFT indexes are designed for aggregating data and improving the performance of aggregate queries. By creating SIFT indexes on fields like Sales Amount and Sales Quantity, you can accelerate the generation of monthly sales reports, as the system can quickly aggregate the data without having to scan the entire dataset.

In summary, NCCI is suitable for analytical workloads involving large volumes of data and complex analytical queries, while SIFT is ideal for improving the performance of aggregate queries and data summarization tasks, such as generating reports or calculating totals. Choosing between NCCI and SIFT depends on the specific requirements and nature of the queries being executed in your Microsoft Dynamics 365 Business Central environment.