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
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 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
SP1 List form performance
tedcjohnston
Member Posts: 41
Since upgrading from 4.0 SP1 to 2009 SP1, my customer list form is performing HORRIBLY. We are a retail company so customer lookup performance is critical. The lookup form is sorted on the Searchname key.
We use SQL2005, the classic client. Not sure if this is part of the problem but we use a SQL Collation- Binary sort order, code page 437 (30).
If I change the primary key in the customer table from No. to Searchname,No., the performance is excellent. However, I would rather not change the primary key of the table as, while I am no SQL expert, I realize this would risk allowing customers with duplicate No. fields as long as the Searchname was different.
I need to keep the customer list sorted by the searchname so that customers with similar names are presented to the user together (otherwise we have a worse duplication problem then we have already).
If the suggestion is to change the collation (I've wanted to for years), I need a collation that treats the dash '-' character as a real character. (We have an "intelligent" item number with the first 2 - 3 characters representing the vendor, then a dash, then a string that identifies the item. Default NAV collation ignores the dash so SL-A124 sorts with SLA-123. We need all SL- to sort separately than SLA-.)
Thank you in advance for your help with this.
Ted
We use SQL2005, the classic client. Not sure if this is part of the problem but we use a SQL Collation- Binary sort order, code page 437 (30).
If I change the primary key in the customer table from No. to Searchname,No., the performance is excellent. However, I would rather not change the primary key of the table as, while I am no SQL expert, I realize this would risk allowing customers with duplicate No. fields as long as the Searchname was different.
I need to keep the customer list sorted by the searchname so that customers with similar names are presented to the user together (otherwise we have a worse duplication problem then we have already).
If the suggestion is to change the collation (I've wanted to for years), I need a collation that treats the dash '-' character as a real character. (We have an "intelligent" item number with the first 2 - 3 characters representing the vendor, then a dash, then a string that identifies the item. Default NAV collation ignores the dash so SL-A124 sorts with SLA-123. We need all SL- to sort separately than SLA-.)
Thank you in advance for your help with this.
Ted
"There are only two truly infinite things: the universe and stupidity. And I am unsure about the universe." - Albert Einstein
Corollary- Build and idiot proof system and nature will build a better idiot.
Corollary- Build and idiot proof system and nature will build a better idiot.
0
Answers
Change the Clustered Index to Search Name, No.
Design the table, Click On View->Keys. SHow column and select Clustered field. Check mark the Search Name, No. Key and you should be set.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Tried that and immediately processes stated failing. Would need to go through the code and change each Customer.GET("No.") type call to either a SETRANGE("No.","Something") or to a GET("Search Name","No.").
I made the mistake of changing the first key to the Search Name,No. As the first key is the primary key to NAV (correct?) this caused the GET statements to fail.
By just changing the Search Name key to clustered, I did not get the errors and the speed is there.
Again, thanks Rashed.
Would be interested to know why the search performance was so bad when the view was sorted on a key of Search Name if anyone knows.
Corollary- Build and idiot proof system and nature will build a better idiot.
My guess if you use sql profiler you'll notice that sql probably is using clustered index instead of the key you have provided.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!