Databases Reference
In-Depth Information
The performance of ODBC applications can suffer if they fail to reduce network
traffic, limit disk I/O, simplify queries, and optimize the interaction between the
application and driver. Reducing network communication probably is the most
important technique for improving performance. For example, when you need
to update large amounts of data, using arrays of parameters rather than execut-
ing an
Insert
statement multiple times reduces the number of network round
trips required to complete the operation.
Typically, creating a connection is the most performance-expensive task
your application performs. Connection pooling can help you manage your con-
nections efficiently, particularly if your application has numerous users.
Regardless of whether your application uses connection pooling, make sure that
your application closes connections immediately after the user is finished with
them.
Making smart choices about how to handle transactions can also improve
performance. For example, using manual commits instead of auto-commit mode
provides better control over when work is committed. Similarly, if you don't need
the protection of distributed transactions, using local transactions can improve
performance.
Inefficient SQL queries slow the performance of ODBC applications. Some
SQL queries don't filter data, causing the driver to retrieve unnecessary data.
Your application pays a huge penalty in performance when that unnecessary data
is long data, such as data stored as a Blob or Clob. Even well-formed SQL queries
can be more or less effective depending on how they are executed. For example,
using
SQLExtendedFetch
instead of
SQLFetch
and using
SQLBindCol
instead of
SQLGetData
reduces ODBC calls and improves performance.
