Table Searching out of control

lloydsmods
Member Posts: 93
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?
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?
If guns cause crime mine must be defective.
0
Comments
-
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?0 -
Using the native DB, version 3.6A. No sumIndex fields.If guns cause crime mine must be defective.0
-
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).Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
Actually...I need to filter a list form, not a report.If guns cause crime mine must be defective.0
-
Add document type to the end of the your secondary key
Probill, Line, Manifest, Client, Carrier, Carrier Service, Origin, Destination,Document type.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions