SQL vs NAV database

I am looking for some awnsers concerning severe performance problems with financials. What is the best database to use with Navision Financials? The Navision database or the SQL server database. I support approx 100 users and have a very high performance server which is still bogged down when usage is high. (Using Navision Database server) Please Help. Thanks

Cannot comment on the use of SQL as we have only ever used the C/Side (Navision) database server. Other comments on this forum lead me to believe if you are having performance problems under C/Side then SQL will not help. Some observations on C/Side which helped us to get performance up from around 3 minutes to post a 30 line invoice to less than 2 seconds for the same transaction set. You must tune two server parameters by hand after installing the Navision server on NT - Cache - the amount of memory that the Navision server is allocated from physical RAM - I have found that about 50 to 60% of physical RAM with at least 80M remaining available for NT (Physical - Navision Cache > 80M). Increasing the Navision Cache to a higher proportion seems to trigger a problem where the Navision Service runs away and consumes 100% CPU doing nothing - this appears to relate to an NT swapping issue ? Commitcache - turn this on to allow the Navision Service to cache database write commits. DO NOT turn this on unless you have the server powered via a UPS. Tuning these two parameters gave us the returns mentioned above. Hope this is useful to you.

After reading much further into the fourms I have gained much insight, but I still have some questions. I currently have a raid 5 with 5 u160 scsi drives. Using a single FDB file. I have plans to install a much bigger disk subsystem. I was looking into 14 drives in raid 0+1 ( Dual channel controller, 7 drives per channel). From what I read the native database is faster. Should I use my 14 drives for raid 1. assign 7 drive letters and split up my 20gb database across 7 drive letters and use my current 5 disks for the OS and for swap space ( on a seperate controller ?). Also, my raid controller has 256mb of battery backed cache, and the server has dual power supplys and dual battery backups. Thanks for the help.

Yes, you should definitely use RAID 1. Navision can use up to 16 drive platters. Part of what you must do is determine your growth rate. If there is currently 20 GB and this is just this year - then you may want to consider going with the SQL option. However, if the 20 GB is over a period of a couple years then stay on the proprietary database. Hopefully, you have at least 1 GB of RAM and at least a 100 MB network. Please describe your current server and network environment. Please also let us know if there is a particular process that’s slow? If so, what is the process?

Thanks for the help. I plan to upgrade the disk subsystem as soon as possible. Network is 100bt now , but I have just ordered a gigabit switch and 2 1000bt cards for the server. The primary problem is with order entry. The software is being used by 60 - 100 call center reps at a time to enter tickets all day long every day. at peak periods entering and posting data comes to a crawl. The server is dual p3 1gb with 1 gig ram and raid 5 now. ( we are losing the raid 5 and moving to raid 1 ). From what I understand , I should use the 14 drives in a raid 1 to assign 7 drive letters and split up the .fdb file across all of the drives? How do I see how much cache is being used now (if any). There is no cache paramater specified in the service startup properties in win2k. I am new to this and did not do the orig. setup, so any help would be greatly appreciated. Mark.

We have had some similar problems and are currently in the process of testing and switching to SQL. A couple of other quick tips to try first are: 1) Go to File, Database, Tables. Highlight everything and click optimize. It will take a few hours but will reduce the size of your database and may improve speed. 2) And the most important thing (besides splitting your database onto multiple physical drives and increasing your cache) is to check your code. It’s amazing what a difference having the correct key in your forms can make. If you are running a form and at the bottom left of the screen it says “Searching table xxx press CTRL+Break to cancel” then your form or code isn’t optimized. 3)Also I have heard quite the contrary with SQL. If you have 20 users or more SQL is supposed to perform about 20 to 25% better. I will respond when I know more as we continue to test over the next couple of weeks. Remember that you can throw all the hardware in the world against C-Side (or SQL) but if your code isn’t optimized then you will always have a problem. Have you done a lot of modifications? Edited by - jwilder on 2002 Mar 17 16:57:04

I have perfomance problems too. There my thought: 1. Navision server: - cache limit is 800 Mb. Navision telling that limit is 1Gb, but server.exe won’t run with more than 800 Mb cache. So adding more than 1Gb RAM is useless. - Adding second procesor useless to, if FDB any base table biger that cache size. Server can’t load table to cache and work with them, so HDD is used and it slow down all system. - So only can help fast HDD (sounds funny ), any known HDD system have I/O times in ms. So it is 20 times slower than RAM. Maybe flash RAM will help in future. - Tables locking. Terrible thing. Navision programers is very frightened by multithreading, so they maked every thing in sequential style (influence of MS-DOS style programing ). So it’s very simple to calculete max load of system. Run couple reports (Users who not post, but mekes reports) If one average post takes 10 s, then you can post only 3600/10=360 posts per h. 2. SQL server: - It’s slower but you can use more processors and more RAM. I hope that can help. - NO TABLE LOCKING. Use SQL if: - Users are blocking one other very often. - you need biger than 64GB Data base. SQL not solves perfomance problems it can solve loacking and size problem !!! How solve Navision server performance problems: - keep fdb as small as you can. - you can use couple navision servers, and use replication for consolidated data. H: Gigabit network. You have 160 Mbits HDD, let it be that cache hit is 25% (RAM speed is ~4 Gbits/s) so average output of fdb kernel is 1120 Gbit/s. Navision using filters and only 20 % of data is outputed - 224 Mbits/s. But in reallity it is less. Check perfomence monitor. But any way write about usage of GBit network. Jason Wilder: “Searching table xxx press CTRL+Break to cancel” It meen cache hit miss. You are right only then optimized table can fit in cache, but non-optimized can’t. Optimization is balansing of B-tree used in keys.

Moved to SQL.