It's All About Performance

Database drivers are all about performance. They have a simple job to do and that is, to send and receive data from the application to the database server in the fastest possible way. Period. This is a fundamental requirement of database drivers, especially the ISAM to SQL kind where the product has to work with a new database without source level changes.

While the driver is the most important component in improving performance, there are some other elements to improving performance, rated (in my opinion)  in order of importance:

1. The database driver
2. The server hardware
3. The database server setup
4. The network

Database Drivers

All drivers are not created equal, (just like a Fiat and Ferrari are not equal.) A lot depends on how the product is engineered, how it handles data and how it interacts with the relational back end.

Over the years, when working on a new driver version, we have always put performace as our number one priority. Our development team's goal is to squeeze out every last bit of performance possible. If we feel that re-engineering our code will give us that extra 10-15 percent, we will do it! For our v10 drivers for DataFlex, we pretty much re-engineered the entire source code to give us twice the performance of our v9 drivers.

We are already seeing the results of these re-engineering efforts.  A recent customer prospect doing a performance comparison between our Flex2SQL for SQL Server and the DAW CK for DataFlex reports Mertech's driver outperforms DAW CK  by 3 to 5 times. In some cases, with larger tables, the performance difference was even higher. Clearly the driver plays a big part in performance.

Another area where the driver matters is in scalability. We recently deployed our Oracle driver at a customer site with 2500+ concurrent users! This customer had tried unsuccessfully to go live  with DB2 for over a year and finally gave up. It was not the DB2 server that couldn't handle the load. It was the DB2 driver.

Server Hardware

When choosing a server hardware,  you need to make sure that you have adequate RAM and you have the fastest possible disk set up. The more RAM you have, the more data will be cached and served from memory. The biggest hardware bottleneck in client server computing  is disk access. Knowing what type of disk configuration will give you better result becomes very important. For example, knowing that RAID 1+0 will be 30-40%  faster than RAID 5 when writing to disk.  There is a huge repository of information on how to optimize a given database server. Include that in your planning process, especially if you are going to have  100+ users doing a lot of disk access.

Database Server Configuration
Most database servers perform quite well out of the  box with minimal tuning requirements. However, if you will have a large number of users, say 100+ users with large amount of data, you will need to make sure that the server is set up to reduce disk contention and disk writes. One way of doing this to by using a smart SAN or by putting your transaction log, index tables and data tables on separate disk spindles. Allocating a large RAM for server process is always good and if you are in Windows, make sure your server has a higher process priority.

Network
The network used to be an issue when we had 10MB networks which was fairly common just a few years ago. This is largely not an issue any longer with faster 100MB and Gigabit switches. However, when running the application over WAN, VPN and even wi-fi network can become a big factor. There you deal with some other issues like network connection glitches and data traffic over a smaller pipeline. The new intelligent data fetching mechanism we implemented in our v10 driver for DataFlex significantly reduces network traffic and also handles network connection issues by automatically retrying when connection to database fails. This means the application doesn't crash when connection to server is lost for any reason.