key limit has reached

Developer101

My customer uses Customer ledger Entries form very frequently and do the filteration on some of the specific fields.

So what I have done I have created one key for those fields in cust. ledger entry table as:

Key : field1,field2,field3,field4,field5,field6,field7,field8

This makes the search faster.

Now customer has requested new field to be added to this search facility. There is not any issue but when I add this new field to they existing above key in the table and save I get error:

New key : field1,field2,field3,field4,field5,field6,field7,field8,field9 (new field in the key)

The error is

---

Microsoft Dynamics NAV Classic

---

The fields in an active key cannot exceed 252 bytes. The size of the field1,field2,field3,field4,field5,field6,field7,field8,field9 key fields in the Cust. Ledger Entry table is 280 bytes.

You must reduce the number or length of the active fields.

---

OK

---

Clearly the limit has reached but what is the solution as it is really required to have that field to be added to that key so that searh works fast.

Please advise any suggestions will be appreciated.

Marije_Brummel

There is no solution. This is the limitation.

Are you really sure adding this will make it faster? SQL Server does not really like long indexes.

Developer101

Right I did not know and really I am not sure of it will make it faster or not but what will I need to do to that new field?

Because a customer could filter on field1 and same time filter on this new field for example. so basically shouldn't all these fields need to be in one key to make search more faster?

If I add this new field and create separate key for it, will it still work as efficient?

Thanks,

Marije_Brummel

I would test it without a key first and see how fast it is.

If you experience it as slow, you can run SQL profiler and catch the query.

Then if you copy and paste the query in SQL Server Management Studio you can analyse is and play around with indexes (SQL name for Keys) until you get the performance you want.

Creating keys for performance is not an exact science.

kriki

Mark Brummel wrote:

Creating keys for performance is not an exact science.

It is a mix between science and art (and some gut feeling...)!

Developer101

Thanks for the input. I will test it by not creating any key.