From SQL to Navision DB

We are having some trouble getting required performance from a customer on SQL and are considering moving their server from SQL to the Navision DB. We know this can be done because we do it all the time for internal development and testing. Has anyone ever done this for a customer? Jim Hollcraft NCSD, NCSP, MCP, MST aka Skater - Unauthorized Navision News

Sounds just like doing a SQL conversion … but without the problems. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________

Whenever possible, running native Navision will be the primary choice, for certain when speed is an issue. But what’s been the reason to go for SQL? Interfacing? Database size? When it comes to interfacing, SQL is still ahead, but database size can be up to 64GB with native Navision also now. BTW, does the SQL server has enough RAM? Recently we upgraded to 1 Gig at a server having performance problems - and it was a tremendous improvement! John

There is 1GB RAM on the server. Dual Pentium 800. Don’t have the page fault numbers so I can’t tell for sure if it has enough RAM, but the database is only about 500MB so 1GB should be enough RAM. BTW, on the Navision database is 3x faster than SQL on some of our tests.

There must be other aspects which gives the speed difference. The customer where we did the RAM upgrade runs with 30-40 users, heavy traffic, huge database (item table alone is 600.000+ records…) on a modest server. A misjudgement made often, however, is that a C/SIDE database and a SQL database are interchangeable without modifications. Although it will run on SQL, that doesn’t mean it’s optimized for the environment. Things to have a careful look at are use of Flowfields (the less the better under SQL!), tablelocking, logical sequence of keys, and many (important) details more. But, as said, when there’s no real need to run on SQL, please do use C/SIDE. John

I have a client where we have several convertions between SQL server and C/Side. This can be done simply by using a Finacials backup. But before you do this you should try to do optimalization on the SQL server. There is a lot that can be done with RAM, Processors and, network and disks. And not to forget apllication changes if your application is from an earlier version then 2.50. We are now running a 100GB database on SQL server 2000 with 100 concurrent users, and the preformance we have now is fully acceptable. I will recommend everyone that is thinking about installing Navision on SQL server to check the infrastructure carefully before installation so can avoid spending lots of time on tuning after the installation. I will also recommend the use of version 3.01 on the client side even if your application is of an earlier version. Inge M. Bruvik

Hi Inge, this sounds like a most impressive implemetation, I for one (and I guess many others out there) would be very interested to know a little more about this. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________

Hi David, I could write a lot about all the things we have done to make SQL server and Navision work well together on this rather large database. And if you have a little pation with me i will post a larger article about it as soon as i find time to write it. Inge M. Bruvik

I agree with Inge’s recommendation of running an Attain 3.01 (actually 3.01A) C/SIDE against existing applications. For the SQL platform there are many performance improvements made in this version, such as: 1- Reusing SIFT sum statements and caching sum values. 2- Using faster SQL statements for FIND(’-/+’) when reading only one row or empty sets. 3- Caching empty set status. 4- Streaming data to the client for caching, with FIND(’-/+’) for small sets. 5- Caching across transactions. These do not require application changes, although the usual guidelines for targeting the SQL platform still apply to application code.