mibuso.com

Microsoft Business Solutions online community
It is currently Fri Sep 19, 2014 1:47 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Tip #4 - Tuning VSift
PostPosted: Sun Apr 12, 2009 3:23 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 4180
Location: Olst
Country: Netherlands (nl)
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.

Go to http://dynamicsuser.net/blogs/mark_brum ... vsift.aspx to read the rest with images...

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Author of the book : Microsoft Dynamics NAV Application Design

MY BLOG : http://markbrummel.wordpress.com


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: