I have too much problem. (Sorry my language) First one is:
I work Navision 5.0 , and my all data store in SQL Server2005. My client company is sales trading company. then too many sales every day. almost 15 people have access. My problem is navision’s all function and report form is very very slow … too slow. sometimes this not responding. The how can i slove this problem?
My second problem is Inventory Valuation report is same very very slow. My client use this report every hours and every day. but this is very slow then they can’t use this report. Maybe what report show the same result?
Please help me all, i can’t solve these problems …
performance depends on various factors…you need to look into all of them.
use sql profiler to monitor block, deadlocks… check inefficient blocks of request and try to tweak the indexes / statistics base on this.
use system monitor to check the ‘avg disk queue lenght’ – for disk read/ write performance, and other counters relating to processor / memory / bandwidth usage…rectify any problem noticed.
check database size and auto growth percentage…check the size of temp db – maintain an optimal setup depending on your organization setup.
check the customization in navision for explicit locks, use of appropriate keys.
Don’t forget to the post(s) that solved your problem. This credits the experts who helped, earns you points and marks your thread as Resolved so we all know you have been helped.
Inventory Valuation report IS slow, if you have many Inventory transactions, and as you described, it is just what you have…
Here I risk being critisized by other community members, but one solution for this is creating this report by some third party reporting tool, using MS SQL as databese it’s not so difficult.
My personal experience (5 years old…) with the same report is, that when I wrote a SQL Stored Procedure to gather & summarize data, then designed a Crystal Reports report using this SP as data source, performance was essentially improved - 5-6 HOURS with Navision standart Inv.Valuation report versus 15 SECONDS with SQL SP + Crystal.
Performance gain is based on fact, that SQL SP gathers, filters and summarizes data on SERVER, but Navision transfers ALL data over netvork to Client, and does processing there. You get not only bad performance of report itself, but high load on your network, plus most of the data are travelling thru network needlesly…
However, this approch is not easy to implement, and require additional expenses (cost of third-party reporting tool, not necessarily exactly Crystal Reports, other exist, too). Then, you need someone with skills in TransactSQL and knowledge of Navision tables structure, and skills with that reporting tool.
So, all of the above is more theoretical discussion, rather than concrete advice for you…
**Search for connections that lock your tables. optimize the statements that **
** **
**produce the locks. **
** **
**Look for statements with bad indexes with ms sql profiler. look how the **
** **
**optimzer work. most of the **
** **
**indexes that the navision installtion produces are very bad. throw them away **
** **
**and build them new at the **
** **
**sql-server without navision. look at key definition and disable **
** **
**maintainsqlindex for the bad keys. **
** **
**there is an introduction for performance optimizing with sql-server on LINK REMOVED BY MODERATOR**
**because its target had nothing in common with SQL or Navision.**
**No advertising of shopping sites, please - as well as other, not relevant to forum theme** !
the post(s) that solved your problem. This credits the experts who helped, earns you points and marks your thread as Resolved so we all know you have been helped.