NCCI: a better replacement of SIFT in a Business Central application

13-11-2024

The nonclustered columnstore index (NCCI) is envisioned to be the successor of SumIndexField Technology (SIFT).


Nonclustered columnstore indexes (NCCI) let you quickly calculate the sums of numeric data type (Decimal, Integer, BigInteger and Duration) columns in Business Central tables. Calculations can be done in tables with millions of records. NCCI optimize the performance of FlowFields and query results in a Business Central application. 


A nonclustered columnstore index (NCCI) is always associated with a table and there can only be one defined per table. Any field from the table, except BLOB data types can be added to the NCCI.


Why to implementing NCCI

In contrast to SIFT keys, which rely on the indexed views feature in SQL Server, NCCI in Business Central use only the nonclustered columnstore indexes feature. No aggregated data is stored in the NCCI, means analytical queries are done at runtime. This implementation also means you do not have to worry about the order of fields added to the NCCI or design specific NNCI for specific flow field scenarios.


With SIFT keys, any insert, update or delete operations to the underlying table will introduce some database locking because the indexed views must be updated as well. The more SIFT keys that are defined on the table, the more index maintenance is needed. This problem does not exist with a nonclustered columnstore index.


NCCI indexes are exposed to SQL Server tracing and tuning tools. The exposes makes it easy for you to assess the cost of maintaining an NCCI. It allows you to make informed decisions about any adjustments that might be required.


SIFT keys versus NCCI

When data is inserted, updated or deleted in a table, the SIFT keys that are defined and enabled for that table are maintained. Maintaining these SIFT indexes has performance overhead. The size of the performance overhead depends on the number of keys and SumIndexFields that have been defined for each table. So give careful consideration to the number of SIFT keys that you define. Only maintain the SIFT keys that are important for your application.

While with an NCCI, only one index structure exists and needs to be maintained. So there is no need to consider which query scenarios should be indexed.


Consider the below performance factors

The below factors that you must take into consideration when you deal with any performance problems that arise include:

  • Do you need an NCCI at all (is it being used)?
  • Which fields do you need to index?
  • Can you disable some or all SIFT indexes and just use an NCCI?


Things to note

Remember to perform tests every time you make any changes to the NCCI structure. You must ensure that the changes that you have made do not cause problems in any other areas of the application. You must also ensure that your changes do not have a negative effect on performance.


The below example shows how to replace SIFT indexes on a table with an NCCI.

The following code creates a table with two SIFT keys defined on it. The Student table is a simple model of students with counts of ECTS points and how many courses they have passed.


table 50100 Student

{

    DataClassification = CustomerContent;


    fields

    {

        field(1; ID; Integer)

        {

            DataClassification = EndUserPseudonymousIdentifiers;

        }

        field(2;Code; Text[50])

        {

            DataClassification = EndUserPseudonymousIdentifiers;

        }

        field(3; FirstNames; Text[100])

        {

            DataClassification = EndUserIdentifiableInformation;

        }

        field(4; ECTSPoints;Integer)

        {

            DataClassification = CustomerContent;

        }

        field(5; NumberOfCourses; Integer)

        {

            DataClassification = CustomerContent;

        }

    }

    // Here are the SIFT keys that the developer added to the table:

    keys

    {

        // Defines the primary key

        key(PK; ID)

        {

            Clustered = true;

        }

        // Defines a SIFT index on Code with aggregations fields for count and SUM(ECTSPoints)

        key(SIFTKeyOnCode;Code)

        {

            SumIndexFields = ECTSPoints;

            MaintainSqlIndex = false;

        }

        // Defines a SIFT index on FirstNames with aggregations fields for count and SUM(NumberOfCourses)

        key(SIFTKeyOnNames; FirstNames)

        {

            SumIndexFields = NumberOfCourses;

            MaintainSqlIndex = false;

        }

    }

}


To replace SIFT keys with an NCCI, just add all fields to the definition of the NCCI and remove the SIFT keys


table 50100 Student

{

    DataClassification = CustomerContent;

    // Defines NCCI to replace the SIFT keys

    ColumnStoreIndex = Code, FirstNames, ECTSPoints, NumberOfCourses;

    fields

    {

        field(1; ID; Integer)

        {

            DataClassification = EndUserPseudonymousIdentifiers;

        }

        field(2; Code; Text[50])

        {

            DataClassification = EndUserPseudonymousIdentifiers;

        }

        field(3; FirstNames; Text[100])

        {

            DataClassification = EndUserIdentifiableInformation;

        }

        field(4; ECTSPoints; Integer)

        {

            DataClassification = CustomerContent;

        }

        field(5; NumberOfCourses; Integer)

        {

            DataClassification = CustomerContent;

        }

    }

    keys

    {

        // Defines the primary key

        key(PK; ID)

        {

            Clustered = true;

        }

    }

}