I still recall the time in 1997 when I presented the first SQL drivers; most DataFlex developers were skeptical. Their first question was "Does this even work?" Followed by "Why should we switch from Pervasive or DataFlex? It works for us and is the "right" fit for DataFlex."
Now, 12 years later, it's clear that we have come a long way. No one questions any connectivity from DataFlex to SQL, but there is a caveat. The driver does a lot of work and it must not be assumed that just because Mertech's solution works, others will work the same way. So what are the differences? Are all drivers the same? While we have answered these questions many times, we have never formally published it.
So here they are:
- OLEDB vs ODBC: Mertech uses OLEDB while DAW CK uses CLI
Microsoft has three different APIs to connect to SQL Server. DBLIB, CLI and OLEDB. DBLIB was a layover from the old Sybase engine that MS acquired in the early '90s that turned into MS SQL Server. CLI was created to provide ODBC access. And then OLEDB was created to replace CLI and provide a universal API to access both SQL as well transaction data. Our first driver, v2.x was developed using DBLIB but then in 1999, we wrote the driver to use OLEDB API. Our reason for choosing OLEDB over CLI was 1) performance 2) better record access for ISAM style databases 3) Connection pooling and better connection handling, and 4) because MS told us 10 years ago that they were internally shifting everything to OLEDB and ADO (built on top of OLEDB) and away from CLI. The DAW CK uses CLI, the ODBC interface which provides access to SQL server through an ODBC driver. Our testing had showed that for a mix of set and record based fetches, we achieved a 15% built-in performance advantage for just choosing the right API.
- Performance
Mertech’s drivers overwhelmingly outperform DAW’s drivers in a real world environment. Our v9 drivers were two times faster than DAW's. The new v10 drivers are 3-5 times faster depending on Indexes being used, etc. This was again highlighted yesterday by a customer in the U.S. who was looking to adopt SQL. They ran the same process on two different machines connecting to the SQL with everything exactly the same except the driver. I asked him what performance difference he saw and he said, "When the process running with Mertech's driver had finished, the process running DAW CK was at 14%." This is consistent with the findings of other developers as well as with our own internal testing. The comparison was done with our latest v10 driver and the latest DAW v5 CK. Needless to say, we have a new ISV partner! This same story was told to me by a Swedish ISV at EDUC in Seefeld. They had deployed five sites with DAW CK, after moving from Pervasive, faced serious performance problems and are now looking to deploy with Mertech.
- Intelligent Data Fetching
With our v10 drivers we introduced an intelligent data fetching mechanism that determines the most optimal number of rows and columns to fetch. This highly complex process significantly boosts performance while reducing network traffic. For example, if you have a selection list based on an index with two segments in a table with 40 columns and say one million rows, we will fetch only the two columns and only the rows you see on the screen. Not only does it result in reduced network traffic but also makes it work with good speed over slow WAN connections.
- Recovering from Connection Problems
Over the years, we have never received any complaints about dropped connections or drivers misbehaving due to a connection hiccup (very short interruption). That is largely due to how OLEDB handles connections and connection pooling. It is more robust at correcting itself from connection glitches than CLI. However, if there was a network outage (somebody pulls the network cable), DataFlex programs would just crash if the driver is unable to handle the effects of losing connection to the server. Our goal for v10 drivers was "high availability, fastest performance." In keeping with that goal, we added robust handling of recovering from connection issues. If a connection drops, the driver will retry and if it fails to reconnect to the server (for instance, if the server is down), it will generate an informative message and ask the user to retry instead of blowing up. Once the connection with the server is established, all transactions and all operations will continue the way they were intended without any loss of data or application crash. The DAW CK doesn't provide the ability to recover from connection hiccups.
- Support for SQL Server Failover and Mirroring
Database Mirroring is an easy way in MS SQL Server to create a high-availability database environment where if the main server goes down, another server picks up the load without the user seeing a difference. A very good demo was given by Gandalf at DISD and then again at EDUC where in the middle of a find operation, he shuts down the server and then when he presses find again the next record is found. In today's always-on 24X7 business operations this is a MUST HAVE feature. With version 10 drivers, this feature is now available. No such feature is offered in the DAW CK.
- Ability to Generate SQL Scripts to Create/Drop Tables and Indexes
One thing we have learned is that some DBAs are pretty strict about who makes changes to their database. They want to see how tables are created and dropped, and for good reason. These operations can lead to serious performance issues. In Flex2SQL, we have always provided a way to create empty tables and indexes (or drop them) so that users could give those scripts to the DBA to work with. This also creates a big advantage for software vendors who can use the scripts to create empty tables during the installation process. No such feature is available with DAW CK.
- No More Structure Caching!
We started doing structure caching (.TD files) back in 1999 after we saw that opening the .INT file is way too slow in DataFlex. With the .TD files, we could store the table structure in a local binary file rather than read it every time from the server. While this led to faster opens, it also resulted in "stale cache" where a customer would change the table structure on the server but it didn't delete the local .TD files, which would lead to errors when the application was run. This was a big support headache for us and to some of our customers. We worked on optimizing how we query tables and now have completely eliminated .TD files. The DAW CK still uses structure caching via a .CCH file.
- Faster Switching Between Application Views
While working on improving opens without caching, we also decided to keep all opened files in memory so that when you switch between programs or views and open and close multiple files, the structures would be available and open will be instant. The end-users can see perceptible improvement in performance while navigating between program components. This function will outperform DAW CK and even the native DataFlex database.
- Support for LOB Columns
With our v10 drivers, we now provide the ability to store large amount of data in LOB columns and fetch them through new commands. Now you can store a large chunk of data and retrieve it in pieces to bypass the 16K field limit in DataFlex.
- Support for SQL Server Partitioning
Flex2SQL allows you to partition your index and data tables into separate filegroups which in turn can reside on separate physical drives. This is an important feature that speeds up SQL Server performance when there is a large number of concurrent users.
- ALTER Table to restructure table
Mertech's SQL Server driver uses ALTER table command to change tables structures when using DBBuilder, using the VDF Table Modeler or using the DataFlex restructure commands. The DAW CK doesn't use ALTER table but instead follows a three step process of first creating a temporary table copying data from the old table to the temporary table, followed by dropping the old table and creating a new table of the same name with the new structure, and finally copying the data back from the old table to the new table and deleting the temporary table. This creating and dropping table cannot be done while the database is online, fragments the database table by creating holes in the database file which effects performance, is slow and interferes with replication.
Mertech: Mar 29, 2024 8:53:00 AM
Riaz Merchant