Databases Reference
In-Depth Information
Why doesn't the driver parse and automatically change the SQL stored pro-
cedure call when it encounters a literal argument so that it can execute the stored
procedure using an RPC? Consider this example:
{call getCustname (12345)}
The driver doesn't know if the value
12345
represents an integer, a decimal, a
smallint, a bigint, or another numeric data type. To determine the correct data
type for packaging the RPC request, the driver must make an expensive network
round trip to the database server. The overhead needed to determine the true
data type of the literal argument far outweighs the benefit of trying to execute the
request as an RPC.
Most applications have a set of SQL statements that are executed multiple times
and a few SQL statements that are executed only once or twice during the life of
an application. Choose the
Statement
object or
PreparedStatement
object
depending on how frequently you plan to execute the SQL statement.
The
Statement
object is optimized for a SQL statement that is executed
only once. In contrast, the
PreparedStatement
object is optimized for SQL
statements that are executed multiple times. Although the overhead for the initial
execution of a prepared statement is high, the advantage is realized with subse-
quent executions of the SQL statement.
Using a
PreparedStatement
object typically results in at least two network
round trips to the database server:
•
One network round trip to parse and optimize the statement
•
One or more network round trips to execute the statement and retrieve
results
Performance Tip
If your application makes a request only once during its life span, using a
Statement
object is a better choice than using a
PreparedStatement
object because the Statement object results in only a single network
round trip. Remember, reducing network communication typically pro-
vides the most performance gain. For example, if you have an applica-
tion that runs an end-of-day sales report, send the query that generates
the data for that report to the database as a
Statement
object, not a
PreparedStatement
object.
