Navison slow on sql 2000

pag

I did a small testprogram to compare the performance of Navision in SQL vs. the native db. This testprogram wrote certain amount of integers in a table and then read them back again.

with million integers it took 9 sec to write them and 28 sec to read using the native database. However at the SQL db it was more than 3 min to write and almost 5 min to read.

Has anyone a comment on this

This test was run on my client computer with no other programs running and the following specs:

Version 2.60.E of Financials.
The SQL is version 2000
OS is Win200 with SP2
Memory is 320 MB
Processor is Pentium III with 1000Mhz

The setup was done according to the book.

Any comments on this would be greatly appreciated.

Luc_VanDyck

According to "the book", you should use at least three disks when installing the Navision SQL option. Did you follow that guideline also?

John_Tegelaar

The most important conclusions from your test are a) that native Navision and SQL are different environments, and b) that in contrary to popular believe, you can't assume that a setup in C/SIDE runs equally fine under SQL. Each environment requires its own optimalization.

Let it be noted, that C/SIDE (or native Navision) is an extremely fast database system, which surely should be the primary choice. You do know you can have databases up to 64GB under C/SIDE now? If you are considering SQL, what are the reasons for that?

John

pag

We have all our systems in ms-sql so for us there are numerous reasons for using ms-sql rather than C/SIDE e.g.

Consistent environment.
Consistent backup.
Easier to maintain a data warehouse.
Easier interaction with other systems.
Clustering and network load balancing.
etc.

It would be nice to hear from users who have installed Navision on SQL in real life and their experience with performance.

John_Tegelaar

Fair enough to look at SQL in your situation. In fact you are giving the top-5 reasons for doing so :-)

My advise is to:

a) Setup a more realistic test. In real-world usage, you're reading/writing from/to many tables, usually a small range of records only.

b) Have SQL server run on a system with plenty of RAM

c) When it is concerning a new system, be up-to-date and do the testing with Attain version 3.01. (not 3.0!) Compared to Financials 2.6, the interaction with SQL Server has greatly improved in 3.01

We have a number of customers running Navision on SQL systems to full satisfaction.

John

stampe

The 64 Gb maximum size of the native database is new information to me, I allways thought 32 was the max size. From wich version can navison use this size (64GB) of database.

Regards

Peter

John_Tegelaar

From version 2.60E up, as far as I know. Your license has to be updated, though, as it's a new granule that allows for 64GB.

John

nlc

Reading data from SQL Server can be quite fast but it depends on exactly how the data is read. Try also with Navision Attain.

Writing data to Navision Server is probably very fast in this test because the client can buffer all the writing (using the the command buffer). This feature could also come in the Navision Attain client for SQL Server...