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
- 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
Options
Developers Toolkit Performance Tuning
FDickschat
Member Posts: 380
At a customer they run the DevTookit DB on a SQL2005 Server. The ToolkitDB runs since several years in different versions. Currently the Toolkit is V3.01.0410 and the DB was updated to NAV2009 (no SP) coming from 4.03. I checked all objects in the DB and they do match the objects from the FOB MS packages with the current Toolkit.
The tookit was never very fast, especially when searching with the "Where used" for Properties. Lately it became even worse (it takes 1-2 hours to run a where used including all properties. Finally I took the time and ran a profiler trace. The result was frightening: Almost all queries are missing the filter for the Version No. (which is the first field in the order by clause) The result are tens of thousands of queries running for 60ms or more reading 900 or more records each (for a simple TOP 1 NULL/*). To make the mess complete indexes without the version no. do not exist.
This also explains why the DB at the customer became slower as they had created several new versions - currently 14. The final solution of course must come from MS fixing the tookit but until that happens (if ever) I would like to discuss feasible solutions:
I see several ways of making the toolkit faster:
- For every version create a new company. Then the missing where should not have an effect.
- Index Tuning / Index Hinting (if necessary)
1 Version per Company:
Easy to implement so I will give it a try tomorrow to see what the result is.
Index Tuning:
Almost all Indexes start with the Version No. So most of the time there is no matching index existing for the query. I tried creating new indexes without the version no. which in some cases worked very well but in some made it worse.
As the toolkit is not like a normal NAV DB (which is different at every customer) but is used in the same way everywhere I think there could be one Index tuning project which should speed up all existing DBs when used.
I have reported my findings to MS (via the NSC which programs the Tookit). I have to assume that I'm not the first one to find this. So maybe one of you guys already has a solution for this.
I'm very interested in your findings / tuning tips or how you use the Toolkit DB.
The tookit was never very fast, especially when searching with the "Where used" for Properties. Lately it became even worse (it takes 1-2 hours to run a where used including all properties. Finally I took the time and ran a profiler trace. The result was frightening: Almost all queries are missing the filter for the Version No. (which is the first field in the order by clause) The result are tens of thousands of queries running for 60ms or more reading 900 or more records each (for a simple TOP 1 NULL/*). To make the mess complete indexes without the version no. do not exist.
SELECT TOP 1 NULL FROM "DevToolkit"."dbo"."Dev Toolkit$Object Reference"
WHERE (("Property Reference No_">=@P1 AND "Property Reference No_"<=@P2))
SELECT TOP 1 * FROM "DevToolkit"."dbo"."Dev Toolkit$Object Reference"
WHERE (("Field_Control Reference No_">=@P1 AND "Field_Control Reference No_"<=@P2))
ORDER BY "Version No_","Field_Control Reference No_","Entry No_"
This also explains why the DB at the customer became slower as they had created several new versions - currently 14. The final solution of course must come from MS fixing the tookit but until that happens (if ever) I would like to discuss feasible solutions:
I see several ways of making the toolkit faster:
- For every version create a new company. Then the missing where should not have an effect.
- Index Tuning / Index Hinting (if necessary)
1 Version per Company:
Easy to implement so I will give it a try tomorrow to see what the result is.
Index Tuning:
Almost all Indexes start with the Version No. So most of the time there is no matching index existing for the query. I tried creating new indexes without the version no. which in some cases worked very well but in some made it worse.
As the toolkit is not like a normal NAV DB (which is different at every customer) but is used in the same way everywhere I think there could be one Index tuning project which should speed up all existing DBs when used.
I have reported my findings to MS (via the NSC which programs the Tookit). I have to assume that I'm not the first one to find this. So maybe one of you guys already has a solution for this.
I'm very interested in your findings / tuning tips or how you use the Toolkit DB.
Frank Dickschat
FD Consulting
FD Consulting
0
Comments
That's another option until MS fixes it or gives us another "option".
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
At the same time I did create a new CSIDE DB locally which is by a factor of 3-4 faster then the SQL DB. I can not test with a CSIDE server at the moment.
So I think the solution for the moment is not to use SQL for the toolkit and wait until MS gives us the other "option"
FD Consulting