GE Project Overview

I had a contract with GE Energy to re-engineer their five-year-old down-hold tool calibration system.  The old system was using the Pervasive 2000 database and would frequently crash.  The system was over-engineered and had many features that were seldom used.  The code for the system was extremely difficult to debug and upgrade.

Database Migration

The database migration was broken up into two parts.  First, migrate the tables from Pervasive to SQL Server.  Second, change the schema to have one primary key for each table and a single foreign key.

The first order of business was to migrate from Pervasive 2000 to SQL Server 2000.  The mysterious crashes started when the RAM on the server was upgraded to over 3GB.  I duplicated the software on a seperate machine and used ODBC to import the Pervasive tables into SQL Server.  After a few tweeks to the code, I had a stable SQL Server flavor of the system without the crashes.  Thankfully, most of the code used ODBC.

The next problem that I discovered was that many of the tables did not have a single unique field as a primary key.  Pervasive allows a primary key to consist of several fields, but SQL Server only allows single field primary keys.  I wrote a SQL script to copy each table from the old database to a new database, and added a new primary key column when necessary.  In order to convert foreign keys, I created a user-defined function (UDF) that took a table name and a set of the old foreign keys and returned a single new foriegn key value.  I was able to convert the data from the old schema to the new at the touch of a button.

Streamlining Code

With the major changes in the schema, all the application code had to be re-worked.  Many of the existing queries used outter joins to return large datasets to the code.  The code would then sift through the data to find the expected result.  This method of letting the application do the inner joins lead to slower response time and higher network traffic.

I redesigned the code to take better advantage of the database server.  All joins were performed directly in the database server.  Whenever possible, only the final result was passed to the application.  The application code was greatly simplified and the network traffic was reduced.

Results

The final analysis of the code was 275% difference in speed from the old system to the new.  This difference created a more productive environment resulting in a savings about about $1.2 million per ye