Info: SQL 3.60/3.70 (NOT 3.70A) Performance Issue

This is for your information, and can be verified with your local MBS office. It applies to both the Client and the Application Server for SQL only. It has been resolved in 3.70A, so you can ignore this is you’re running 3.70A or greater executables. Background: There is a known performance issue on these versions regarding a batch insert machanism employed by C/SIDE to optimize INSERTs. The inserts are cached on the client and sent in batches, making use of a SQL Server ODBC driver optimization for improving insert throughput. For tables with SIFT keys the inserts are placed into a SQL Server temporary table, then bulk-moved over to the base table so that the SIFT trigger will fire only once. Unfortunately this often leads to a reduced performance for every-day database activities, depending on the width of the table (therefore it is somewhat arbitrary) and should be disabled. In future fixes this will be removed entirely. Solution: To disable this, use Query Analyzer and switch to your SQL database. Execute the following: UPDATE [$ndo$dbproperty] SET diagnostics = diagnostics | 65536 Do this for each of your SQL databases. Clients must reconnect to the server/database for the setting to take affect.

Thanks for this information Robert. Does this performance problem manifest itself in any particular part of the application? i.e. can we identify which of our customers might be effected by this by which part of Navision they are using? Chris.

Sorry I can’t be specific about the application area because it is a C/SIDE mechanism that is independent of the application code. Anything with more than 5 consecutive inserts will cause this batching which in turn can cause the performance problem. Any posting tasks will trigger it I suspect.

Hi, is this topic valid for 3.01 and 3.10 versions ?? Nico

No. 3.01 and 3.10 did not have this mechanism in C/SIDE so they don’t have the problem. Many sites have observed the degradation when going from 3.10 to 3.60.

Robert, Can you confirm that this advice is still valid for all installed databases? We are on version 3.70A and I have noticed that the value in our DB still reads 0. I hate messing with live databases so confirmation would be good before I attempt to change it. Regards Meint

This problem was removed in September 2003 and 3.70A was released in March 2004, so there is no problem in 3.70A - you don’t need to make this change, keep it at 0. Thanks for pointing it out. I will update the original post.

by the way: if the problem was fixed in 09/03 is the problem also be solved in version 14998 (17-11-2003)? this version is installed in our company…

Build 14988 (hotfix 12) is OK as well, so are: 14555 (hotfix 10) and 14068 (hotfix 5). But 13643 (hotfix 3) and 13164 (the RTM) suffer from the problem.