Databases Reference
In-Depth Information
From a design point of view, you need to know that when you use object-
relational mapping tools you lose much of the ability to tune your database
application code. For example, you are not writing the SQL statements that are
sent to the database; the ORM tool is creating them. This can mean that the SQL
statements could be more complex than ones you would write, which can result
in performance issues. Also, you don't get to choose the API calls used to return
data, for example, SQLGetData versus SQLBindCol for ODBC.
To optimize application performance when using an ORM tool, we recom-
mend that you tune your database driver appropriately for use with the database
your application is accessing. For example, you can use a tool to log the packets
sent between the driver and the database and configure the driver to send a
packet size that is equal to the packet size of that configured on the database. See
Chapter 4, “The Environment: Tuning for Performance,” for more information.
Many factors affect performance. Some are beyond your control, but thoughtful
design of your application and the configuration of the database middleware that
connects your application to the database server can result in optimal perfor-
mance.
If you are going to design only one aspect of your application, let it be data-
base connections, which are performance-expensive. Establishing a connection
can take up to ten network round trips. You should assess whether connection
pooling or one connection at a time is more appropriate for your situation.
When designing your database application, here are some important ques-
tions to ask: Are you retrieving only the minimum amount of data that you need?
Are you retrieving the most efficient data type? Would a prepared statement save
you some overhead? Could you use a local transaction instead of a more perfor-
mance-expensive distributed transaction?
Lastly, make sure that you are using the best database driver for your applica-
tion. Does your database driver support all the functionality that you want to use
in your application? For example, does your driver support statement pooling?
Does the driver have runtime performance tuning options that you can config-
ure to improve performance? For example, can you configure the driver to
reduce network activity?
