Well, at Convergence 2007 in Copenhagen it was announced (Session: NAV Keynotes) that in Q1 2008 the SP1 for NAV 5.00 would be released (dates are subject to change, of course). During the "Meet the Experts" session little more information about the SP's content was published.

As we had no chance to really looking into the technical details, this post is more guessing than knowing.

Here is what I considered most important:

1. SIFT Tables will be replaced by Indexed Views
2. "Buffered Inserts" will be introduced
3. General fixes & improvements

 General Improvements:

Starting from the end, these fixes will included e.g. improvements in the client site's memory management, the translation of C/AL into SQL etc. ... let's say the "usual" stuff which generally should stabilize the NAV client.

"Buffered Inserts":

Currently a INSERT in NAV is instantly triggering an INSERT into the SQL table. With "Buffered Inserts" this should not happen anymore, the C/AL INSERTS will not result in an instant insertion into the physical table; this should be done block-wise at the end of the transaction (COMMIT).

The current way of inserting data is somewhat problematic as we have a steady mixture of read and write transactions. This keeps the server quite busy, causes lots of network traffic and is locking records/resources pretty soon which further increases the risks of blocks.

With the new way, the general process comes closer to "recommended" SQL Server treatment: Read Data - Process Data - Write Data

As the physical insertion will happen at the very end of a transaction, we should get rid of Dirty Read problems; and blocking problems should be reduced.

On the other hand I wonder, where and how this "data-to-be-inserted" will be buffered? The NAV client resource-requirements are pretty much under-estimated; clients that process a huge data-volume may also need sufficient CPU power, plenty of RAM, Gigabit, fast disks, etc.. Even now many clients are "under-sized", so with "Buffered Inserts" I could imagine that these problems would be worsened ...

I would appreciate if we - the developers - get a chance to define whether we want to buffer or not.

Indexed Views:

This, I think, is the most important issue. I briefly mentioned this in another BLOG ... and hell, what fuss did I raise!

As far as I understood, "Indexed Views" will completely replace the SIFT management as we know it. Currently the SIFT information is aggregated data from a source table (Ledger Entry) which is physically saved in a dedicated SIFT table.

The problem here is the SQL Server site SIFT "Triggers" which are performing quite poor; and due to the physical write transactions also performance is reduced, blocking issues are raised. Of course, SIFT structures could be optimized (http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx)

Now, these SIFT tables will vanish; instead there will be Views which display the aggregated values of the source table; mabe like this:

Source Table (My Ledger Entry) "SIFT" Index: Item No, Variant Code, Location Code - SumIndexField: Quantity

New SIFT View (estimate):

create view dbo.SIFT_View with schemabinding as
select
  count_big(*) as "count"
 ,"Item No_" as "f2"
 ,"Variant Code" as "f5402"
 ,"Location Code" as "f8"
 ,sum(Quantity) as "s12"
from
  dbo."...$My Ledger Entry"
group by
  "Item No_" ,"Variant Code" ,"Location Code"

(I guess MS will stick to the old f_ and s_ naming)

A View is actually just a predefined SELECT statement on one (or more) table(s). Hence, it will be necessary - as today - to have an appropriate index on the source table to perform good (MaintainSQLIndex = TRUE).

As a View does not store any physical data, the write performance should basically better that with the old SIFT tables (no additional "Costs Per Record").

Now to improve the reading performance - when querying data from this View - one (or more) indexes could be created on it. At leat a Clustered Index would be required:

create unique clustered index SIFT_Idx on dbo.SIFT_View (f2, f5402, f8)

To further improve this, it may be feasible to have additional Non-Clustered Indexes (see also my other BLOG mentioned above):

A "Covering Index"

create nonclustered index SIFT_Idx_2 on dbo.SIFT_View (f2, f5402, f8, s12)

This should provide reasonable speed when reading data from the view. So far so good.

But I'm somewhat concerned about several issues:

• How will we be able to define for which "bucket" we want ta have a View? Now we have all buckets stored in one SIFT table; will we have one View per bucket in future? This would create a hell of objects in the database!
• How will the indexes really look like?
• Even though writing data in physical SIFT tables "costs" something, there is nothing faster than reading one single (aggregated) record from a real table. On tables with millions of records it could be an advantage to have the old physical aggregation, instead of the "virtual" aggregation of the View. I'm afraid that with super-size tables the "Indexed Views" may be a disadvantage ...

Well, as mentioned in the beginning: this is just my assumption - guessing, not knowing. Once SP1 has been released (Q1 2008), we will see how close this guess comes to the truth ...