Performance/speed degradation

pmusu

Hi,

We have a client who is experiencing a gradual decrease in speed on a particular process. There has been no recent changes to the code. They are using Navision 3.70B with SQL Server.

Has anyone encountered a similar problem? A few things they have done is rebuilt the indexes (using SQL maintenance). Last year the performance on this particular task was 5 seconds. It now takes over 1 minute.

We cannot seem to replicate the problem on our local system (they provided a backup). Using client monitor we are not noticing any bottleneck in the process (31ms).

Any suggestions?

Thanks in advance.

garak

Did you check the querys from NAV to SQL with the SQL Profiler and the client monitor?
Which process has problems with the performance?
What for kinds maintenance jobs exist and how often runs this jobs?
There are some codechanges or some indexchanges?

pmusu

garak wrote:

Did you check the querys from NAV to SQL with the SQL Profiler and the client monitor?
Which process has problems with the performance?
What for kinds maintenance jobs exist and how often runs this jobs?
There are some codechanges or some indexchanges?

Thanks for the quick response.
I have used client monitor. They have a customized form (non-standard). It is an quote/order form. They click on a button where you change the status.

In client monitor the statement which takes the most processing time is 31ms. I am focusing on that process. I originally asked the question because I am still not sure what is causing such a decrease in performance.

Thanks and regards

garak

Take also a look to your SIFT Structure and check also with the Profiler on the SQL Server.

Here a link with the needed indicators: viewtopic.php?f=34&t=32657

stryk

With NAV 3.70 I suppose you use SQL 2000 ...
One crucial aspect is to optimize the SIFT structures of important tables (= many records, many SIFT, heavily used) by reducing the number of "buckets" - see: http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx

And: how big is your DB? How many users? What are the hardware specifications of the SQL Server?

Last but not least I recommend to search this forum for "SQL Performance" and you'll find tons of advices to optimize NAV/SQL performance!

Regards,
Jörg