Speed Up Your SQL Inserts

atabase performance is one of the most critical characteristics of almost any application, and for the developer or DBA it usually depends on how fast they can retrieve data. Hence, many a performance optimization and tuning book discusses the ways to make queries faster. Even RDBMS makers understand the need for fast data retrieval and provide different tools (indexes, configurations options, and so on) to facilitate it. However, database performance is not always represented by speed of data retrieval. In some situations, database performance relies on the speed of data inserts.

Suppose you need to design a control measuring system or survey site that stores results in a database. The task seems to be pretty straightforward, but a closer look at the specifications reveals that everything is not as simple as you might assume:

1. You need to process and insert a very high volume of incoming transactions into a database.
2. You have to provide 24x7 availability.
3. The number of fields (parameters) that has to be stored in the tables can vary widely. For example, the number of questions differs in different surveys (i.e., number of controls or elements on the Web pages), or the number of detectors or measuring devices can differ for different processes in the control measuring system.
4. The application will be used not only for data inserts but also for data retrieval, though not very intensively. (For extensive analyses, you can create an OLAP system, but that is another story.)