Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K 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
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 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
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Optimazation questions
Bgestel
Member Posts: 136
I'm Readin a lot of Sql NAV performance related literature lately.
I have the following questions:
Question 1:
- We do not use the variant code in our solution.
I assume that it would not be usefull to have the variant code in de Sql key, or is it? In the standart nav code there are several setranges on this field, since we do not use it the setrange will always result in a statement in the where clause stating variant code="". So is it usefull to have a key on something thats always empty. i dont think so, just want to be sure.
Question 2:
- conserning granularity:
The books en papers i read say that it is good to have the more granular fields in the beginning of the key
so : Document no, document type is better than document type, document no .
Will this slow down selects that only filter on document type??
Lets say the key is document no, posting date, <five more fields>, document type.
Will this slow down selects that only filter on document type??
Question 3:
In 5.0 Ms added some sqlkeys to tbl 32 , why did they choose to manualy add the entry no to every key.
Question 4:
Purely hypothetic:
Lets say that every query done to the sales line has a filter document no in it. And there are never more than 500 lines in an order, invoice ... .
It is possible that additional filters are set. Is it in this case the right way to go to only have a key on document no in the sales line table. (my idea: result set is 500 an sql is fast enougn to sort out the records you need, acording to the extra filters)
Qustion 5:
A good link that explains the various execution plans?
Thanks in advance.
I have the following questions:
Question 1:
- We do not use the variant code in our solution.
I assume that it would not be usefull to have the variant code in de Sql key, or is it? In the standart nav code there are several setranges on this field, since we do not use it the setrange will always result in a statement in the where clause stating variant code="". So is it usefull to have a key on something thats always empty. i dont think so, just want to be sure.
Question 2:
- conserning granularity:
The books en papers i read say that it is good to have the more granular fields in the beginning of the key
so : Document no, document type is better than document type, document no .
Will this slow down selects that only filter on document type??
Lets say the key is document no, posting date, <five more fields>, document type.
Will this slow down selects that only filter on document type??
Question 3:
In 5.0 Ms added some sqlkeys to tbl 32 , why did they choose to manualy add the entry no to every key.
Question 4:
Purely hypothetic:
Lets say that every query done to the sales line has a filter document no in it. And there are never more than 500 lines in an order, invoice ... .
It is possible that additional filters are set. Is it in this case the right way to go to only have a key on document no in the sales line table. (my idea: result set is 500 an sql is fast enougn to sort out the records you need, acording to the extra filters)
Qustion 5:
A good link that explains the various execution plans?
Thanks in advance.
**********************
** SI ** Bert Van Gestel **
**********************
** SI ** Bert Van Gestel **
**********************
0
Comments
2) Definitely because SQL has to scan the whole table. But if your table only contains records of the document type you filter for, it won't change anything.
3) Well, in theory it is not necessary because SQL adds the clustered key to the other keys, so even if it was not there, it is added anyway. But probably they took in consideration that someone might change the clustered index, so in this case it is better to have the entry no. in the key because the order by that Navision sends to SQL always contains the primary key fields at the end. And this can confuse (in some cases) SQL into using a wrong key.
4) In theory it is enough. But if possible I would add another field (preferably a small one that is used a lot to limit the records to <100). It also depends on how many times you need the index to search on. If it is once a minute, no problem with only "Document No.". If it is once a millisecond, I would add the field for sure.
5) ...
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!