Pervasive.SQL

How does Pervasive.SQL stack up as a SQL-based database server?

When you're considering a SQL database solution for your company, you have several options. In addition to Microsoft’s SQL Server, there are products such as Sybase SQL Server, Sybase SQL Anywhere, and Oracle Workgroup Server. However, to support these high-end database solutions, you'd have to add more hardware and more personnel.

Pervasive.SQL is the newest SQL-based, networkable database engine for Windows NT Server. Pervasive.SQL claims to offer all the features of a high-end database solution, without the need for an expensive database administrator (DBA) to install and support it.

Like its competitors, Pervasive.SQL offers all the features you expect to find in an enterprise-level database engine product. The software is fully Year 2000 (Y2K) compliant and offers standard database features such as stored procedure support, triggers, declarative referential integrity, automatic recovery and roll-forward capabilities, and support for binary large objects.

So how does Pervasive.SQL differ from other SQL products, and how does its performance compare to these other databases? To see how well Pervasive.SQL stacks up against the competition, I installed the software on a system running Windows NT Server and put it through its paces.

Low, Expandable Resources
Pervasive.SQL is, very simply, a client/server database engine. It is not an integrated development environment, such as Microsoft Visual FoxPro. Although you use the product to manage your information resources, you can't use it to develop programs; you still need some type of development environment (e.g., Microsoft Access, Visual Basic—VB, Visual FoxPro) to design the application that will manipulate information within the Pervasive.SQL database.

Unlike other SQL-based database engines for NT, Pervasive.SQL has very modest system requirements because of several unique technologies that Pervasive Software uses to deliver the product to you. These technologies include the company's MicroKernel Database Engine and dynamic file allocation.

Pervasive.SQL's microkernel database architecture uses a modular design to deliver a highly flexible database to you. The database engine requires as little as 5MB of RAM, although the published specifications call for a minimum of 16MB of RAM. As demands on your database environment increase, the product automatically tunes itself to meet the increased demand and allocates more resources as necessary. Furthermore, you can configure the software to release resources it no longer needs. This last feature is helpful in environments where you have short periods of heavy processing. For example, assume you are in a manufacturing environment where you run shop-floor and materials requirements planning (MRP) jobs at night, and process orders during the day. During the night, your database engine requires more RAM to accommodate the large bill-of-material jobs you must run before you can schedule your production environment the next day. In a traditional environment, you would allocate a set amount of memory to the server to accommodate this requirement, and you wouldn't be able to recoup the lost memory when it's not in use. Pervasive.SQL lets you do just that, so you can use scarce resources such as RAM where they are most needed.

Another useful, but potentially dangerous, feature of Pervasive.SQL is its dynamic file allocation capabilities. Products such as SQL Server require you to pre-allocate a set amount of disk space for your database. However, when you create a database with Pervasive.SQL, the product allocates the minimal amount of space it needs for its system tables and indexes. This minimalist approach lets you create a database with as little as 50KB. Then, as you add data to your database, Pervasive.SQL expands the database to accommodate the increased storage requirements. This capability lets you maximize your disk space, but it has a potentially dangerous side effect: If you have little available disk space, and Pervasive.SQL needs to expand its files to accommodate your data requirements, you might run out of disk space in a production setting.

Pervasive.SQL Features
The Pervasive.SQL software consists of the database engine and a half-dozen GUI applications to manage your database environment. These applications include Data Decryption Field (DDF) Ease, SQLScope, Rebuild, Monitor, User Count Administrator, and SmartScout. The two applications you'll probably use the most are DDF Ease and SQLScope.

DDF Ease is a database definition utility. The utility uses a Windows Explorer-like interface, with left and right panes within the program window. The left pane contains a list of your Pervasive.SQL databases. You can drill down through the databases to expose each table's field definitions and index definitions. The right pane contains information about the item you select in the left pane. Screen 1 shows statistics on one table in the Pervasive.SQL demonstration database.

SQLScope is an interactive SQL execution utility, similar to ISQL/W in SQL Server. After connecting to a database, you enter a SQL statement in the window and click Execute. The program sends the request to the Pervasive.SQL database engine, which returns the results and displays them below the request. Using this utility, I was able to create complex queries, including queries that used aggregate functions and different joins. Screen 2 shows a basic SELECT query from the student table in the sample database.

The other utilities accompanying the software perform basic administrative functions. The Rebuild utility is useful only if you are running a pre-7.0 version of Pervasive.SQL. This utility automatically rebuilds data files for use with the version 7.0 file format. This utility can enhance your system’s performance if you are upgrading from a prior release of the software. Monitor is similar to SQL Server’s Performance Monitor. Using this utility, you can monitor Pervasive.SQL’s performance in several categories. You can examine various microkernel parameters, specifically the current, peak, and maximum values in a range of parameters. The software monitors the number of files open, number of handles in use, clients connected to the system, worker threads currently in use by the database engine, locks in place, transactions executed, and the number of licenses in use. The User Count Administrator lets you increase your license count. When you purchase more licenses, Pervasive Software supplies you with either an electronic key code or a license disk. This utility either reads the disk or prompts you for the electronic code, validates it, and increases your license count appropriately. And finally, the SmartScout utility tests your system’s connectivity to the Pervasive.SQL database server.

AutoRun
My test copy of Pervasive.SQL came on one distribution CD-ROM and one license-count disk. Overall, installation was rather painless, but I did encounter some unexpected problems.

For installation and testing, I used a Digital Prioris HX590 dual-processor Pentium system with 4GB of stripped disk storage and 96MB of RAM, running Windows NT 4.0 Server and the latest service packs. I also installed SQL Server 6.5 with the latest service packs on this machine set for manual start.

I used a different Windows client machine to properly test the database product. Although I could have tested the software from the host system, I felt this configuration would have been an unfair test because SQL Server would consume more memory and affect system performance. For my second test machine, I used an American MegaTrends-based dual-Pentium II system running NT Workstation 4.0, the complete Office 97 suite, and Visual Studio 97. I connected the client and server systems using a thin-net TCP/IP link.

I installed Pervasive.SQL in two phases. In the first phase, I set up my NT server with the Pervasive.SQL database engine. In the second phase, I installed the client software on my NT workstation.

The server-side installation proceeded smoothly (so I thought). After launching the setup program, I chose to perform a custom installation. I went through several screens of prompts asking me which components to install, after which the installation process ran unattended. When the installation finished, a series of stress tests ran indicating the database engine was online and operating correctly. I rebooted my system and began installing the software on my workstation.

On my workstation, I chose to perform a typical installation, and the install program ran uninterrupted for 5 minutes. However, after completing the installation, I was unable to establish a connection to my Pervasive.SQL server. Upon further investigation, I found the Pervasive.SQL database engine had failed to start when I rebooted my NT server. I couldn't start the server by manually starting the task, and I couldn't find the specific failure code in the online documentation. I uninstalled the software and started the installation from scratch, but this time I chose to perform a typical installation on the server. After I reinstalled the software and rebooted the server, I was able to access the Pervasive.SQL database engine from my client system.

Success Rates
To test Pervasive.SQL’s performance against SQL Server, I established a test customer database using both database engines. The database consisted of eight fields that totaled 113 bytes in length. Table 1 shows the test database structure. The database contained several index values: The customer number was the primary key, whereas the remaining values were not unique indices.

Using VB, I created a program to import 10,000 records into both a SQL Server and a Pervasive.SQL database. I configured two ODBC data sources on my client machine and built a table of data values in memory. Then, using a standard data control’s record set in a for/next loop, I performed a series of .AddNew/.Update loops to populate each record. I repeated this process twice—once for the SQL Server table and once for the Pervasive.SQL table.

The results were surprising and disappointing. To load the 10,000 records into my SQL Server database took 2 minutes and 31 seconds; with Persuasive.SQL, the same process took 35 minutes and 15 seconds. Considerable disk thrashing occurred while Pervasive.SQL dynamically allocated the space it needed to store the database information. Of course, it's important to note that SQL Server performs the database space allocation process up front.

Unfortunately, this poor write performance continued throughout my testing. I used Microsoft Access to attach the customer table from both Pervasive.SQL and SQL Server. Next, I created a query to update the balances for all customers in Rhode Island (532 records) by $100. Running this query against the SQL Server table took only 7 seconds; running it against the Pervasive.SQL table took 26 seconds. To select records, I used the State field, which was an indexed field in both databases.

Pervasive.SQL and SQL Server had no appreciable performance difference in SELECT queries. Both simple and complex JOIN and UNION queries from both databases completed within seconds of one another.

Persuasive.SQL—Is it Right for You?
Although the product claims that it needs no DBA to run it, the software definitely needs a database-savvy person, especially if you intend to do any new development. Database administration involves more than monitoring and tuning the database (i.e., only a database-savvy person will be familiar with problems associated with proper creation of database tables, constraints, stored procedures, triggers, and more).

One area where Pervasive.SQL outshines SQL Server is its price. The base price for Pervasive.SQL, which includes a 10-user license, is half the base price of SQL Server and 10 licenses. Pervasive.SQL is a real bargain, especially for small and medium-sized business markets where the staff is not necessarily in place to support a large database installation. You can download a trial version of the software from Pervasive Software's Web site at http://www.pervasive.com.