Although Open Database Connectivity (ODBC) provides the world with a standards-based mechanism for interconnecting applications and databases, ODBC is often not the best system to use. One generally finds that, in real-world applications, the more general something is the less efficiently it runs. ODBC is no exception - when compared with a system-specific data connector (i.e. one that is proprietary to a particular brand of database) the ODBC interface will generally run more slowly and won't let you take advantage of features that are specific to your particular RDBMS.
If your world is based on Oracle, then, you would generally choose to build your in-house applications using the native Oracle programmer interfaces - which exist for most popular development platforms. One of the concepts you'll need to embrace, if you care to any extent about the performance of your programs, is the Bind Variable.
The obvious way to specify a query in an application is to simply build it as a string and pass it to the database through the drivers. Imagine your program asked the user for a customer ID and then retrieved that customer's name and address from the database. You'd probably write something like this:
custID = askUserForCustomer() myQuery = "SELECT ctitle,cforename,csurname FROM customer " myQuery &= "WHERE ncustid = '" & custID & "' " execQuery(myQ)
It's a simple way to build your query but in fact the database has to work quite hard whenever you send it this type of query. This is because it has to parse and compile the query and then work out, using its statistical information repository, an execution plan that makes best use of whatever indexes and keys are available. The more complex the query (the more tables that are joined together and the more indexes it has to choose from) the more work Oracle has to do to compile and plan the job before it actually starts to run the query and extract the results.
Oracle therefore provides a concept called the Bind Variable, which allows you to reduce the time the system spends analysing and compiling your queries. The example above is actually quite acceptable if you're only going to run that query every once in a while - but if (as is likely with a "find customer details" type of query) you're going to run it a lot in each session, you want to avoid repeated instances of execution planning and query parsing. This is achieved by splitting the static bit of the query from the bit that changes.
It's actually not all that different from thinking of your query as a function, not an SQL statement. You replace the part of the query that changes with a variable name, and then pass in whatever value you want to (in our example the customer ID) via that variable. So you'd say something like this:
custID = askUserForCustomer() myQuery = "SELECT ctitle,cforename,csurname FROM customer " myQuery &= "WHERE ncustid = :customerid " myDB.Parameters.Add "customerid", custID, 1 Set myRecordSet = myDB.DbCreateDynaset(myQuery,0)
The first time you run this query, you'll see no benefit from introducing the variable ":customerid" - in fact, it may take a millisecond or two more to run than the static query we had in the beginning, as it has to deal with the extra parameter that we've just introduced. For subsequent runs of the query, though, you're making immense savings, simply because the DBMS doesn't have to re-parse, re-compile and re-plan the execution.
The reason is simple: it's seeing the same query as it did before. The query planning process doesn't need to know precisely what values you're looking for - it just cares about what tables you're querying and what fields. So the next time we submit this query in our session, the DBMS says to itself: "I've already run this one" and simply pulls out the execution plan it used before. So our task becomes a "plan once, run many" order of complexity, not a "plan many, run many" one.
Real performance increases
There's no denying that bind variables can be fiddly to get to grips with but it's really no harder than passing parameters to stored procedures. The benefit you get, though, can be immense - we have come across instances where moving to bind variables has lopped 75 percent off the execution time of a modestly-loaded application.
Given that using bind variables brings other benefits too, such as more flexible run-time error handling and less silly mistakes in queries (if you're using variables, you're usually forced to be more rigorous with your data type casting). For a minor initial learning curve penalty, the gains are significant. So there's no excuse not do use them - QED.