Adding new keys - Calculating the size

beetlestone

Is there a way of trying to evaluate the amount of space a new key will take in a database.

And before anyone mocks me....

We know how many records are in the table.
We know the structure of each field type - CODE 10, DECIMAL etc
We know how many Sum Index fields we want - not relevant, but...
And we also know how the size of the record, Table size and Optimisation.

So is there a 'formula' which could be looked at which would help you to make a decision about whether to add a key or not.

We are talking about tables with over 2,000,000 records, so adding keys has a dramtic effect on free space, especially as we are running CLASSIC server.

kriki

I don't know if there is a formula to calculate it. Probably there is. But : To create a key or not to create a key, is NOT the question. (At least not anymor e since every MB of DB-space had to be bought ; and nowadays diskspace is cheap enough).
The question should rather be : What do I gain in reading performance and what do I loose in writing performance.
E.G.1: if it is for a report you use only once a month, and it is a table on which a lot of writes happen (like G/L Entry or Item ledger Entry), it is better not to create it and try to use an existing key that maybe is not perfect.
E.G.2: if it is a key to be used for searching a record in a posting codeunit (and you post a lot, and it is a big table), in this case it is better to create it.

DenSter

It also depends on whether you are on C/SIDE or SQL. On SQL Server if you create a key in Navision it creates an index on SQL Server, which will take up space. You can elect not to create the SQL Index, in which case there is no SQL Server index, but you can still use the key in C/AL code. The SETCURRENTKEY command in Navision does not result in SQL Server actually using the index, so it might be for nothing anyway. As far as sumindex fields, it does not matter to SQL Server if it gets the sum out of a SIFT key table or from the actual table, so you might want to consider creating the key in Navision, but not creating the index on SQL Server. Like Alain says, it depends on the frequency that you will use the key for.

It also depends on the SIFT levels you intend to maintain. If there is a date field in your SIFT key, then you may want to consider only turning on the ddaily one and disabling the other ones. If you intend to run the report once a month, then you want to maintain only the month one. There's lots of things to think about when adding a key.

beetlestone

Thanks for that, but all I need to ascertain is whether it is possible to judge the amount of disk space that adding and extra key will use.

We are using NATIVE Navision server - I mentioned this.

And the reasons for determining whether the key is added is so that we can decide whether the 'feature' is one that will be added or discarded as not advisable.

I have done a few checks myself in a custom table with 10,000 records, and comparing Table size, Record size etc when adding new keys, adding fields to existing keys, and the effect of different field types in the keys.

I was just hoping that someone had already looked into this, so that I didn't have to reinvent the wheel.

DenSter

I don't know how you would calculate the extra space for an extra key. I experimented with a copy of an 8GB customer database, and the size was cut in half just by disabling all the keys in the Item Ledger Entry table. That's right, the size dropped from 8 GB to little over 4 GB just by disabling all secondary keys in ONE table. Granted they had a truckload of sumindexfields, but still, it can be that order of magnitude, even in a native database. Of course if you don't have that many sumindexfields, the difference may not be that dramatic.