Howdy, Stranger!
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
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 310 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Keys on Customer ledger entry
Belias
Member Posts: 2,998
I am currently analyzing a database that has performance problems, and digging through the modified tables (i've not the availability of customer data, i'm just doing a first analysis) i've seen this keys in table 21:
all maintained, No VSIFTS
bold lines is a standard key
My (second) thought is about the selectivity of "Document no.", which is usually very high, isn't it?So, why maintain all those keys? :-k
i know that some versions of NAV behaves "stupidly" trying to mach the WHERE and the ORDER BY perfectly, but is there a reason other than this?
EDIT(IMPORTANT): I'm supposing that all the queries use document no. as one of the filters. (which usually happens in NAV)
Thanks in advance
all maintained, No VSIFTS
bold lines is a standard key
my first thought was that the underlined keys could be unmaintained and then summarized with this one:Customer No.,Batch No.,Posting Date,Document Type,Document No.
Customer No.,Batch No.,Entry No.,Document Type,Document No.
Document Type,Document No.,Document Occurence,Customer No.,Open
Posting Date,Document No.
Document Type,Customer No.,Posting Date,Document No.,Currency Code
Document No.,Document Type,Customer No.
after this i noticed that a lot of keys in this table (which is not "sql friendly", you know), are based on "Document no." and something else.Document No.,Customer No.,Batch No.,Document Type
My (second) thought is about the selectivity of "Document no.", which is usually very high, isn't it?So, why maintain all those keys? :-k
i know that some versions of NAV behaves "stupidly" trying to mach the WHERE and the ORDER BY perfectly, but is there a reason other than this?
EDIT(IMPORTANT): I'm supposing that all the queries use document no. as one of the filters. (which usually happens in NAV)
Thanks in advance
0