SQLPrepare

With ODBC, you can do an SQL statement in one function call:

 SQLExecDirect("<SQL statement>") 

or you can split it into two function calls:

 SQLPrepare("<SQL statement>") SQLExecute() 

Which is more efficient? We measured the relative performance of both methods and found that SQLPrepare takes a significant fraction (averaging about 30%) of the total time. If a statement is complex or if the number of rows affected is small, then it helps performance to take the preparation out of the loop, like this:

 SQLPrepare("<SQL statement>") loop {   SQLExecute()   } 

Taking an invariant assignment out of a loop is called hoisting . We won't waste time showing it's a good idea because that's obvious. What's important is to know when hoisting is not worthwhile.

If your DBMS caches SQL statements and reuses the plans, then hoisting is automatic so it's not worthwhile to do it yourself. For example, you would see no gain if you use Oracle.

Hoisting may not be helpful if your intent is to generalize an SQL statement to take advantage of SQLPrepare . For example, suppose you have these three queries:

 SELECT * FROM Table1 WHERE column1 = 'A' SELECT * FROM Table1 WHERE column1 = 'B' SELECT * FROM Table1 WHERE column1 = 'C' 

To execute the queries, you think that this looks like a clever idea:

 SQLPrepare(    "SELECT * FROM Table1 WHERE column1 = ?") SQLBindParameter() /* to associate the ? with variable x */    x = 'A'      SQLExecute()    x = 'B'      SQLExecute()    x = 'C'      SQLExecute() 

For the general case, changing a literal to a parameter is good because the DBMS then has no need to reparse the statement multiple times and also because there will be fewer different statements to process. Great but not always!

The danger with this idea is that, although the three SQL statements look similar, the execution plans could be very different if the value A occurs far more frequently than B in the index. If the optimizer knows what the literal is, it also knows whether it's common or uncommon and can modify the execution plan accordingly . If you don't give the optimizer that information, then it has to use a generalized plan that might not be optimal at all.

Statements are not re-prepared when statistics change. Thus, if the loop contains an implied transaction end, the preparation may become invalidmaking hoisting definitely not worthwhile. For example, suppose you're updating:

 SQLPrepare("<UPDATE statement>")   loop {     SQLExecute()     } 

This sequence is doomed if the DBMS is in auto-commit mode, and if the DBMS invalidates PREPARE statements whenever it commits. Admittedly though, you can get around that difficulty by calling SQLGetInfo(SQL_CURSOR_COMMIT_BEHAVIOR) and SQLGetConnectAttr(SQL_ATTR_AUTOCOMMIT) .

Here's a final reason to be cautious of hoisting. In early versions of Microsoft, SQLPrepare causes the creation of a temporary table. Make no mistake, the Boy Scout motto is correct. It's just that "Be prepared" doesn't always mean "Call SQLPrepare ." If you're afraid of it, then at least try this: Limit what users can enter (notice that most Internet dialog boxes give only a few options), and depend on the DBMS to reuse plans for queries that are exactly the same.

The Bottom Line: SQLPrepare

If a statement is complex or if the number of rows affected is small, take SQLPrepare out of the loop.

Do generalize an SQL statement to try and take advantage of SQLPrepare unless the query plan depends on a skewed set of data values.

If you must SQLPrepare , limit what users can enter, and depend on the DBMS to reuse plans for queries that are exactly the same.