SQL in a Nutshell, 2nd Edition

Bound Parameters

One of the easiest ways to optimize a slow database application that executes many similar SQL statements is to parameterize the most commonly used SQL statements. Parameterization is a way of reusing a SQL statement by writing it with placeholders for frequently changing values. This provides two major benefits: the potential for reduced server roundtrips and better processing efficiency on the database server, since the server doesn’t need to parse, plan, and optimize the execution of frequently used SQL statements on every execution.

Parametrized statements are useful in using string or binary values within SQL statements that contain unfriendly characters, such as quote marks or terminal NULL characters that indicate the end of a string. Usage in this way provides security benefits to a database application where user input is used within statements. Not binding a parameter coming from an end user could allow clever modification of the statement in a way that may divulge secret information from the database.

Tip

Since there is a slight cost involved in setting up a parameterized statement, it’s good practice to only parameterize statements that will be executed at least three times prior to the statement closing. If a statement is only executed once prior to freeing the statement, the costs of preparing the statement and binding the parameters could result in a performance net loss to the application.