Table Searching out of control

lloydsmods

If you use a primary key field in a secondary key, is there any potantial problem? I have a table that has the following primary key:

Document type, Probill (essentially Document), Line

We have a secondary key:

Probill, Line, Manifest, Client, Carrier, Carrier Service, Origin, Destination

This causes searching hell and locks the table for as much as 10 minutes while the system searches the table.

If I drop Probill and Line from the secondary key, its blazing fast. Problem is, I need my list to be in Probill, Line order.

How can I make this sort the way I need it, without sacrificing performance?

ara3n

Are you using SQL or Native?
These advices are for sql
If you are using 4 sp2. You can have different SQL index than a navision key.
For older version
Also you uncheck maintain SQL index. and create a third index that is fast for which you can maintain the index.

Also do you have sumindex fields for the secondary key?

lloydsmods

Using the native DB, version 3.6A. No sumIndex fields.

kriki

Create the index you need for searching fast the table. Put the records you found in a temptable.
Then you run your report on the temptable with the correct index. (best create a new table to be used only as temptable with the fields of the real table but with the index you need for printing. You don't need to put this table in the license to use it as a temptable).

lloydsmods

Actually...I need to filter a list form, not a report.

ara3n

Add document type to the end of the your secondary key


Probill, Line, Manifest, Client, Carrier, Carrier Service, Origin, Destination,Document type.