Background: This client had been using a custom built data extraction tool for many years, but had found that it could not keep up with a recent increase in data volumes. The output files extracted with this tool were used as input into another system. The tool was written in c++ using Pro*C to connect to an Oracle 8.1.5 database. The current output they were getting was 180,000 records per hour. They wanted to avoid the costs of developing another extraction program and came to us to see if would increase the performance without re-writing the program.

Summary of solution: We changed the logic of the embedded SQL in the tool, introducing a more 'set-based' design which allowed more efficient access to data and parallel output streams. With these changes it was possible to output over three million records per hour.

Total Time from start to solution: 3 days   Total Charge Time: 16 hours

Workflow - how we got to the solution:

Customer	Sql Tuning Service
The client contacted us directly to discuss the problem. The program had been developed in-house a number of years ago. They had retained the source code but the original developers had moved on. The program was written in C++ with Pro*C and was running against an Oracle 8i database. Their goal was for the program to be able to handle the current live requirements of 300,000 per hour and be able to handle up to 500,000 records written per hour.
	We sent a small script to the Client which gathered some information about the database schema, and set up targeted logging in the database for a test run of the program.
The client ran the script and a test extraction. They sent us the outputs of the script and test extraction logs. This took about 2 hours to do.
	We analysed the log files and the source code of the program. We could see a design change in the logic of the program would increase the performance significantly. We made the following offer to the client - a fixed charge of 16 hours work to provide a solution that would allow 500,000 records per hour to be extracted.The standard Sql Tuning Service guarantee applied; a fixed price and no payment would be required if we did not meet this performance.
The client agreed to engage our services.
	We redesigned the Pro*C code to use a more set-based rather than procedural logic. This resulted in less querying of the database and allowed us to extract data in parallel. Once we had the program running correctly against out test database, we sent the code to the Client.
The Client's team compiled our new version and tested the program. They found that they could extract 3.1 million records in one hour.