Case Study Info

SQL Query Optimization

The Challenge

Rapidly increasing data processing demand in client systems are causing database queries to hang in the server. Delays are occasionally reaching into minutes, rendering a time-to-response which is completely unacceptable for client PCs.

Solution Overview

There are two traditional solutions to this problem. The first is to increase server power, either dedicating more of a single server or adding more servers to the system, depending on the size of the operation. The first option usually incurs considerable expenditure on hardware and server software licensing. Generally speaking, the resulting performance increases are linear, at best–two servers, twice the power, and so on.

The alternative, tuning the RDBMS (especially an Oracle Server), improving database structure, and optimizing SQL queries may allow an increase in performance in excess of an order of magnitude on current hardware. This is simply a matter of the database adding several indexes on certain tables/fields, restructuring of few SQL queries. In this instance response time decreases from 2-3 minutes to 7 seconds.

If the database system is already optimized, of course, a hardware upgrade is the only workable option.

Softage develops data access middleware solutions to provide optimization for SQL queries. Also we can optimize your existing SQL queries in MS SQL Server, Oracle RDMS or any other ANSI SQL based RDBMS in case your data amount rapidly grows and you have unacceptable performance break downs.

Development Process Overview

  • ODBC Driver development
  • JDBC Driver development
  • Web integration
  • Legacy database access
  • Data Warehousing solutions
  • Enterprise Applications Integration

Technology Domains

C/C++, SQL, MS SQL Server, Oracle