Database application design is one of the factors that
affect application performance. Review this section for details about
application design considerations that can help you to maximize the
performance of database applications.
Application processes, concurrency, and recovery
All SQL programs execute as part of an application
process or agent. An application process involves the execution
of one or more programs, and is the unit to which the database manager
allocates resources and locks. Different application processes might
involve the execution of different programs, or different executions
of the same program.
Concurrency issues
Because many users access and change data in a relational
database, the database manager must allow users to make these changes
while ensuring that data integrity is preserved.
Improving insert performance
Before data is inserted into a table, an insert search
algorithm examines the free space control records (FSCRs) to find
a page with enough space for the new data.
Efficient SELECT statements
Because SQL is a flexible high-level language, you can
write several different SELECT statements to retrieve the same data.
However, performance might vary for different forms of the statement,
as well as for different optimization classes.
Guidelines for restricting SELECT statements
The optimizer assumes that an application must retrieve
all of the rows that are identified by a SELECT statement. This assumption
is most appropriate in online transaction processing (OLTP) and batch
environments.
Specifying row blocking to reduce overhead
Row blocking, which is supported for all statements and
data types (including LOB data types), reduces database manager overhead
for cursors by retrieving a block of rows in a single operation.
Data sampling in queries
It is often impractical and sometimes unnecessary to access all
of the data that is relevant to a query. In some cases, finding overall trends
or patterns in a subset of the data will suffice. One way to do this is to
run a query against a random sample from the database.