Lets talk about KEYS

to start off I’m not using SQL Ok I have always left the KEYS alone, maybe added a few here & there where needed. If I wanted to “clean up” some of the table keys such as in the item table. I have 15 enabled KEYS some added for sorting. Quote from Navisioner.com & I feel the same

quote:

you must have a key for every sorting order you need in a report (or form). That may be very taxing in many situations, specially if you have in mind that indexes take up lots of database space, and also consume time whenever you add, change or delete records.

As a rule of thumb I will never disable the PRIMARY KEY but can you, without problems, disable keys that have no SumIndexFields or KeyGroups? AND if I do so will help increase the “speed” of filtering the table? for example I would like to disable the following KEYS: Low-level Code Production BOM No. Routing No. Inevtory Posting Group General Prod Posting Group. We never sort by these ever and we do not manufacture. ok shoot…

If you disable those keys, then any programs (reports, forms, codeunits, etc) that uses any of those keys will return with an error. Turning off those keys will reduce the BLOB size of the table. I’m told that reducing keys will enhance the performace as well although I haven’t confirmed this.

You can disable/delete these keys. The performance will be enhanced.

quote:

If you disable those keys, then any programs (reports, forms, codeunits, etc) that uses any of those keys will return with an error.
Originally posted by achowtc - 2004 Nov 03 : 15:56:16

pretty much what i needed to know. if we do ever run a report (which we haven’t ever) ever gives us an error then i can always enable it. I have just noticed that when I do optimize tables that there are many many keys it goes through., many of when we will never use. As our database increases (now approching 15gb in a few years) hopefully turning off useless keys only there for report sorting (none with sum ndex fields 'such) will slow the the rapid increase & speed up many tables. i guess giving it a try will finally give the answer. I just wanted to be sure before it was attempted. Others please feel free to add a thought - thanks

Keys are used for 2 purposes: 1. For filtering on field values, i.e. searching for records, 2. For sorting records by the key fields. Navision can do (1) without the aid of a good key, but cannot do (2) without an appropriately ordered key. So you must be sure that when you remove a key, it is not still being used for searching purposes anywhere even though you are confident that it is not used for sorting. If it is, you will get a read performance hit if the key would have been used, even though of course you will get an update performance gain by not having the key.

quote:

pretty much what i needed to know. if we do ever run a report (which we haven’t ever) ever gives us an error then i can always enable it. I have just noticed that when I do optimize tables that there are many many keys it goes through., many of when we will never use.
Originally posted by savatage99 - 2004 Nov 04 : 02:41:00

Long long time ago, there was a possibility in Navision to GROUP keys and activate/deactivate them during runtime. The table is still there (2000000203,Database Key Groups). Maybe we get these nice function back some day (as well as the Global-Filter-Function… do you read that over there in Denmark??)

quote:

i guess giving it a try will finally give the answer. I just wanted to be sure before it was attempted. Others please feel free to add a thought - thanks
Originally posted by savatage99 - 2004 Nov 04 : 02:41:00

Hm, as you know the key you are deactivating, you may use the developers toolkit (if you have access to it) to find out whether it is in use or not and where. Kind regards Walter

Walter, The Database Key Groups are still working. He could associate all the disabled keys to a Database Key Group and if ever a user gets an error about a missing key, it is easy to re-enable it from the File->Database->Information->Tables->Key Groups window. Or was there a possibility to do it using C/AL code back in the DOS version?!?..

quote:

Hm, as you know the key you are deactivating, you may use the developers toolkit (if you have access to it) to find out whether it is in use or not and where.
Originally posted by walter@kirz - 2004 Nov 04 : 07:46:37

The good old method of exporting all objects to text and searching the file for any occurence of the key fields also works if you cannot use the Developer’s Toolkit. [:)]

quote:

The Database Key Groups are still working.

I know. And as far as I remember, you could activate a key-group by using a C/AL command (I would not bet on that anymore. The last time I was using the good old DOS-version was 1997.). Maybe somebody else could help here… However, the WAS a global-filter command (dbGLOBALFILTER).

quote:

The good old method of exporting all objects to text and searching the file for any occurence of the key fields also works if you cannot use the Developer’s Toolkit. [:)]

…if you can export the objects to text. But then, normally, you have access to the developer toolkit…

Please allow me to be a little bit pragmatic about this. You never told what problem you are trying to solve. My guess is either a performance issue, or a ressource problem, i.e. diskspace. If it is diskspace, disks and arrays of these are relatively cheap and easy to get and install, and will solve the problem. If it is performance, as pointed out by others too, performance may improve, og degrade depending on how the system uses the keys you are disabling. But performance problems may come from various sources, and you should investigate each of these separately. Generally in my experiance performanceproblems on a native server are solved most effectively by increasing RAM and Navision cahche (both DBMS and object), changing to a bigger and faster disksystem, increasing the effective bandwidth between the client and the server and upgrading to a faster processor on the server, in that order. You do not win much performance by disabling a key I believe. [:)]

Yes, reducing the keys (or disable them) will reduce the size of your database. Now, for a discussion a little off topic… Key groups is a nice feature in Navision. However, I don’t know any programmers that uses it…

quote:

Key groups is a nice feature in Navision. However, I don’t know any programmers that uses it…
Originally posted by achowtc - 2004 Nov 05 : 15:39:59

Touché [:)]

quote:

You never told what problem you are trying to solve. My guess is either a performance issue, or a ressource problem, i.e. diskspace.
Originally posted by PVL - 2004 Nov 05 : 03:00:10

Actually there are no problems. I was just curious about disabling unused KEYS & the possible problems that might occur.[8D]

Hi, I would recommend importing the objects to Navision Developers toolkit where you can see where the keyes are used. There is no point in focusing on a small table so the tables to focus on are the “entry” tables for the Item Ledger, Value Entries etc.

Here’s an interesting observation I have made. I disables some KEYS in Posted Invoices & Posted Sales Line Tables. It has grinded my Crystal Reports to a halt (reports that used these tables). A 2 minute report became 30. So my conclusion is that disabling Keys can change some kind of structure in the table that also effects outside applications using c/odbc.

Navision’s use of the database - and this also applies to c/front - is such that you cannot order records without a key to support that ordering. c/odbc is different in that it must provide some decent general SQL capabilities and that includes using ORDER BY for any field combinations not just those supported by a key. If there is no key c/odbc will still function - it will pull all matching records to the client and order them itself - which is very inefficient. This gives your bad performance.

You can disable keys, but I wouldn’t disable the last two on your list. They are used in posting codeunits. Disabling keys won’t speed up filtering. It will have some effect on inserting new records. When filtering it is important to use correct key that has fields in required sequence (don’t ever “skip” a field in a key while setting a filter).