SIFTing through the CRUD

CRUD (Create, Read, Update, Delete) has to be one of my all time favourite computer acronyms. I also like PICNIC (Problem In Chair Not In Computer) but that's a whole different story.

SIFT (Sum-Index Flow Technology) is a clever trick used by NAV to give you incredibly fast displays of aggregated totals. The Net Change and Balance fields on the Chart of Accounts screen are probably the best known examples. These fields are instantly drawn and can be filtered by dimension, posting date, etc. I have not seen this in the other ERP systems I have used and it is pretty impressive and powerful. However, with great power comes great responsibility and SIFT has its price. To get this fast performance, NAV makes a trade and instead of doing the work at the time of reading the data, NAV does the work when you create, update or delete the data. This can be OK for records that are not updated very often but you do need to be careful how many FlowFields and SumIndexes you add to tables. There's no such thing as a free lunch (unless of course you work in sales.)

I recently tried to make an update on a table with a relatively large number of records. My SQL script was simple and all it did was set one field to be the product of another two fields (e.g. Line Amount = Qty * Price). The dataset being updated contained just over 2 million records and I killed the query after 3 hours of execution. I couldn't understand why the thing was taking so long. Then I remembered SIFT! I don't pretend to be a SQL expert and there are lots of clever people out there that have written lots of clever stuff on database optimisation but what I do know is that even if my SIFT update takes a fraction of a second then 2 million fractions of a second can be several hours.

When you have a SIFT index on a table, NAV adds a SQL Trigger to the table with lots of fancy SQL code to update a number of SIFT tables with the aggregated totals. Fortunately, NAV provides the ability to disable the maintenance of SIFT tables in SQL. You can also fine-tune how many levels of data should be maintained which can again improve performance. When you go into the table designer and view the keys you can show an extra column called MaintainSIFTIndex. I went into my table designer and un-ticked this field for the SumIndex keys that were totalling the field I was updating. I compiled the table which dropped the table trigger that maintains the SIFT tables and deleted the SIFT table.

I then ran my SQL query again. It completed in 8 minutes!

I went back in to the table designer and re-activated my SIFT maintenance option and re-compiled. NAV put the triggers back and re-created my totals. This took 5 minutes. As you may know reads are the least expensive type of database operation with inserts, updates and deletes being considerably more time consuming. It is much faster to get the sum index totals updated all in one go rather than bit by bit over and over for every record that is updated.

I also have a data-conversion routine for a NAV upgrade that takes a very long time to execute – the next time I run it I am going to disable my SIFT maintenance on the tables first (and possibly disable the SQL maintenance of some of the indexes which also add time to transactions). Once the routine has completed I will rebuild the indexes and SIFT tables – it will be interesting to see how much of a performance gain I get by doing this.

I see from the posting on the Sustained Engineering Team Blog that the imminent SP1 release for NAV 5.0 will allow the Indexed Views feature of SQL 2005 to be used for SIFT instead of the current method. It will be interesting to see what affect this has on performance.