Tip #4 – Tuning VSift


This Blog Article was brought forward from my old blog

After all the theory about VSift in my previous blog posts now a tip about how to tune VSIFT.

Before I continue first this.

VSift is not a bad technology but the implementation in NAV has one big downside. The good old SIFT levels have disapeared.

In older versions of NAV there was a SIFT Level property. This enabled you to decide which SIFT level best suited your implementation.

Many people say: Disable all top levels. But I disagree on that. Measure the exact levels you need and only enable those or better: creating your own levels.

That is what this blog is all about: creating your own levels. Before SQL came I used to tune big C/Side databases and also then we did not have this SIFT Levels property, so we had to create our own.

To know how that is done it is critical to know how the NDBCS driver works. This driver decides which VSIFT to use. This driver is as stupid as SQL is intelligent. (Sorry Dean). As far as I know it has always been this way, also in the Natvive days but maybe some MSFT folks can post about this.

When NAV need to calculate a flowfield it reads the Key table top down and grabs the first key that matches the definition.

Now since selectivity and number of reads/records is extremely important for your performance it can be interesting to create keys that are narrower and reduce the number of reads.

Example:

When I open the G/L account in NAV this query is executed:

SELECT

SUM(“SUM$Amount”) FROM dbo.”CRONUS Nederland BV$G_L Entry$VSIFT$1″ WITH(NOEXPAND) WHERE ((“G_L Account No_”=@P1

))

With an average of xx reads.

Now when I disable this VSIFT$1 level, NAV has to go to the next available level.

When the other fields in the index are heavily used and have a lot of values, the number of reads can be huge!

So what if you have a customer who uses NAV for 7 years thus having potentialy 2500 posting dates.

In the old days you would simply enable the top SIFT bucket but what with VSIFT?

Answer; Create a new key above the old keys so the driver will pick that up and give it priority.

Voila, it is as simple as that.

BUT: Be carefull: You could potentialy be changing business logic. NAV has the nasty habbit of allowing halfsyntaxed SETCURRENTKEY statements. In that case it uses the first key that it can find that starts with those fields. Maybe that in another blog… Stick out tongue

 

Advertisements
This entry was posted in Tips and Tricks. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s