Developers Toolkit Performance Tuning

FDickschat

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.

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.

ara3n

I mainly use the toolkit on Native db and have much better performance.
That's another option until MS fixes it or gives us another "option".

FDickschat

I tested in a fresh DB with just one Version. That is as expected much faster.

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"