Partial Index for sql 2008 and Navision

NavStudent

Hello I was reading about Partial Index on wiki and there is also an attached pdf that talks about it in detail.

http://en.wikipedia.org/wiki/Partial_index

PostgreSQL supports this feature.

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.

A major motivation for partial indexes is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.

Having this feature will greatly help performance in navision.
Specially in the ledger tables, where we have fields like Open, that are really not good sql indexes but if we only maintain when the open is TRUE. This will improve the updates to the ledger a lot.

Another example would be. Only maintain indexes where posting date a year old. This way you don't maintain indexes older than a year.

I know this is a long shot because SQL has to implement it first, then Navision has to implement it, but it definitely makes sense for ERP system to have this feature.

jfalkebo

In general we could do much more in NAV to make it easy for customers to setup the system in such a way that we don't ask SQL server to maintain unused or partially unused indexes. Key groups is a move in the right direction but it is an area that we need to improve.

Partial indexes is supported with the next version of SQL server (2008), they are called filtered indexes.

Even SQL server 2005 supports features that would provide value in this context, namely partitioning. It is specifically targeting the scenario where old data could be taken taken offline until needed or put on a slower disk-set.

So we do recognize the problem but unfortunately I can't tell you when we are going to do something about it.

colin_leversuch-roberts

this type of feature is only likely to be useful in really large databases - my understanding is that the average Navision database is very small ? As in all things once you hold the data in memory then many of these features have less impact - you'd probably gain better performance by adding extra memory to your server.

NavStudent

The performance increase you get with partial indexes is in the writes to the db. So no matter how much memory you add to the server, you still have to write to the disk.


With predefinining the filters on a key, if a user selects the key, the form can automatically filter and users cannot change the filter on those fields.


Anyways, as long as it can be done on sql side, the db can be twicked further.