SQL performance general considerations

Get answers to the when, what, and how questions when designing your ODBC environment.

Performance of SQL in application programs is important to ALL server users, because inefficient usage of SQL can waste server resources.

The primary goal in using SQL is to obtain the correct results for your database request, and in a timely manner.

Before you start designing for performance, review the following considerations:

When to consider performance:

SQL Tables with over 10,000 rows - Performance impact: noticeable
SQL Tables with over 100,000 rows - Performance impact: concern
When repetitively using complex queries
When using multiple work stations with high transaction rates

What resource to optimize:

I/O usage
CPU usage
Effective usage of indexes
OPEN/CLOSE performance
Concurrency (COMMIT)

How to design for performance:

Database design:
- Table structure
- Indexes
- Table data management
- Journal management
Application design:
- Structure of programs involved
Program design:
- Coding practices
- Performance monitoring

The SQL Reference book contains additional information. You can view an HTML online version of the book, or print a PDF version, from the DB2® for i SQL Reference topic in the IBM® i Information Center.