key limit has reached

Developer101Developer101 Member Posts: 528
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.
United Kingdom

Comments

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • Developer101Developer101 Member Posts: 528
    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,
    United Kingdom
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • krikikriki Member, Moderator Posts: 9,094
    Creating keys for performance is not an exact science.
    It is a mix between science and art (and some gut feeling...)!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Developer101Developer101 Member Posts: 528
    Thanks for the input. I will test it by not creating any key.
    United Kingdom
Sign In or Register to comment.