Databases Reference
In-Depth Information
The Issue
Response time has become unacceptable. Why?
Here are some questions to ask:
•
Has the volume of users increased?
•
Has the network configuration changed?
•
Has anything changed on the database server, such as another database sys-
tem was installed on the server?
•
Is the configuration of the driver correct for this type of application?
•
Is the application using connection pooling and statement pooling?
•
Is the application returning only the data it needs, and is it returning the data
in the most efficient way?
Thinking Through the Issue
Let's think about what we know:
•
Many environment issues can cause slow response time, such as insufficient
bandwidth, physical memory, or CPU. For this scenario, let's assume that
more memory or CPU cannot be added.
•
Many, many users access the application, but the application is not config-
ured to use connection pooling.
•
The use of large database protocol packets is not a good idea in this type of
application. Check the database protocol packet's size configured in the dri-
ver. Often the default size is not the size that you should use for OLTP-type
applications.
•
One of the easiest ways to improve performance is to limit the amount of
network traffic between the database driver and the database server—one
way is to write SQL queries that instruct the driver to retrieve from the data-
base and return to the application only the data that the application requires.
Let's assume that the application is coded with optimal SQL queries.
The Resolution
Because more memory cannot be added to the database server, this issue must be
resolved in the application and the database driver. The solution is twofold:
•
You can optimize the application to use connection pooling. As we have
stated several times throughout this topic, connection pooling can provide
significant performance gains because reusing a connection reduces the
