2012-03-19Bind Parameter

This section covers a topic that is missing in most SQL textbooks; parameterized queries or bind parameters.

Bind parameters—also called dynamic parameters or bind variables—are an alternative way to pass values to the database. Instead of putting the values directly into the SQL statement, a placeholder (like ?, :name or @name) is used. The actual values are provided through a separate API call.

Even though it is very handy to write the values directly into ad-hoc statements, there are two good reasons to always use bind parameters in programs:

Security

Bind variables are the best way to prevent SQL injection.

Performance

Databases with an execution plan cache like SQL Server and the Oracle database can re-use a previously prepared execution plan when executing the same statement again. It saves the effort to rebuild the execution plan but works only if the SQL statement is exactly the same. If you put different values into the SQL string, the database will handle it like a different statement and create the execution plan again.

When using bind parameters, you just write the placeholders into the SQL string. The database treats it as the same statement, even when executed with different values.

Quick answers instead of long searches!
Instant Coaching is the online consulting service by the author of this page.

But there are exceptions. If the affected data volume depends on the actual values, like in the following examples:

99 rows selected.

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20;

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_PK |   99 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBSIDIARY_ID"=20)

An index access delivers the best performance for small subsidiaries, but a TABLE ACCESS FULL can outperform the index for larger subsidiaries:

1000 rows selected.

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30;

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           | 1000 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES | 1000 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SUBSIDIARY_ID"=30)

Putting the values directly into the SQL statement allows the Oracle optimizer to use the histogram on the SUBSIDIARY_ID column to get the row count estimates. Even though the optimizer creates both plan variants for both queries, their cost values differ because of the different row count estimates. When the optimizer finally selects an execution plan it takes the plan with the lowest cost value for each query. For the smaller subsidiary, it is the one using the index.

But the cost of the TABLE ACCESS BY INDEX ROWID operation is highly sensitive to the row count estimate. Selecting ten times as many rows will elevate the cost value by a similar factor. The overall cost value with index is then even higher as for a full table scan. The optimizer will therefore select the execution plan without index for the bigger subsidiary.

When using bind parameters, the optimizer always gets the same row count estimates and cost values. In the end, it will always select the same execution plan.

Tip

Columns with status flags such as “todo” and “done” often have a nonuniform distribution. Not using a bind parameter for these values can potentially deliver a better execution plan. But you have to take other precautions against SQL injection then.

Comparing the optimizer to a compiler, bind variables are like program variables while values in the SQL statement are more like constants. The optimizer can take the values from the SQL statement into account, just like a compiler can evaluate constant expressions. Bind parameters, on the other hand, are not visible to the optimizer.

From this perspective, it is a little bit paradox that bind parameters can also improve performance. Because not using bind parameters enables the optimizer to take the best execution plan. But the question is: at what price? Generating and evaluating all explain plan variants is a huge effort as well. An effort that only pays off if you do not get the same result every time.

Tweet this tip

Tip

Not using bind parameters is like recompiling a program every time.

The decision to build a specialized or generic execution plan is a dilemma for the database. To know if a specialized execution plan pays off, the database has to create the execution plan first. That is, however what the execution plan cache should avoid. Databases employ heuristic methods to cope with this dilemma, but with very limited success.

As developer, you can help the database solving this dilemma by using bind parameters specifically. That is, you should always use bind parameters except for values that should influence the execution plan. That can be the case for unevenly distributed status columns, but also when using partitions. In the latter case, the actual values may affect the partitions being accessed.

Tip

In case of doubt, use bind parameters—just to prevent SQL injections.

The following code snippets are examples how to use bind parameters.

C#

Without bind parameter:

int subsidiary_id;
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name" 
                 + "  from employees"
                 + " where subsidiary_id = " + subsidiary_id
                 , connection);

With bind parameter:

int subsidiary_id;
SqlCommand cmd =
       new SqlCommand(
                      "select first_name, last_name" 
                    + "  from employees"
                    + " where subsidiary_id = @subsidiary_id
                    , connection);
cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);

See also: SqlParameterCollection class documentation.

Java

Without bind parameter:

int subsidiary_id;
Statement command = connection.createStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = " + subsidiary_id
                  );

With bind parameter:

int subsidiary_id;
PreparedStatement command = connection.prepareStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = ?"
                  );
command.setInt(1, subsidiary_id);

See also: PreparedStatement class documentation.

Perl

Without bind parameter:

my $subsidiary_id;
my $sth = $dbh->prapare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = $subsidiary_id"
                );
$sth->execute();

With bind parameter:

my $subsidiary_id;
my $sth = $dbh->prapare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = ?"
                );
$sth->execute($subsidiary_id);

See: .

PHP

Using MySQl, without bind parameter:

$mysqli->query("select first_name, last_name" 
             . "  from employees"
             . " where subsidiary_id = " . $subsidiary_id);

With bind parameter:

if ($stmt = $mysqli->prepare("select first_name, last_name" 
                           . "  from employees"
                           . " where subsidiary_id = ?")) 
{
   $stmt->bind_param("i", $subsidiary_id);
   $stmt->execute();
} else {
  /* handle SQL error */
}

See also: mysqli_stmt::bind_param class documentation. The PDO interface supports prepared statements as well.

Ruby

Without bind parameter:

dbh.execute("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = #{subsidiary_id}");

With bind parameter:

dbh.prepare("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = ?");
dbh.execute(subsidiary_id);

See also: Ruby DBI Tutorial.

Tip

More examples using ORM Tools

The SQL standard defines the question mark (?) as placeholder for positional parameters only. Most databases and abstraction layers (JDBC, perl DBI, …) offer a proprietary extension for named parameters nevertheless. The Oracle database is the opposite example. It does not support the question mark natively, but only named parameters introduced by a colon (:name). In that case, the abstraction layers emulate the question mark support.

Note

Bind parameters cannot change the structure of an SQL statement.

That means, you cannot use bind parameters for table or column names. The following bind parameters will therefore not work:

String sql = prepare("SELECT * FROM ? WHERE ?");

sql.execute('employees', 'employee_id = 1');

If you need to change the structure of an SQL statement, use dynamic SQL.

Cursor Sharing and Auto Parameterization

The more complex the optimizer and query are, the more important execution plan caching becomes. SQL Server and Oracle have therefore mechanisms to parameterizise queries automatically. Value in the SQL string can be converted to bind parameters. This feature is called CURSOR_SHARING (Oracle) or auto parameterization (SQL Server).

Both features are workarounds for applications that do not properly use bind parameters.

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
229
views

query regd the CBO decision

Apr 17 at 10:27 Hulda(suspended)
index-choice optimizer
0
votes
3
answers
2.0k
views

Examples for Function Based Indexes?

Mar 25 at 15:52 Castorp 1
function-based
0
votes
1
answer
610
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql