Jenny and bind variables

[paul wheaton]

Currently, Jenny uses bind variables only for the fields that the developer specifies that is what they want.
It has been suggested that I should make the default be to use bind variables.
What do you guys think?

[Stefan Wagner]

Who or what is Jenny?

[Jeanne Boyarsky]

Stefan,
Jenny is a db code generator.
Paul, What would be the advantage to using it always by default?

[paul wheaton]

I've been told that if you use bind for everything, Oracle can do some optimizations that it cannot do otherwise. An interesting point. I cannot come up with a substantial reason to not do otherwise (other than the fact that it's more 'normal' SQL - possibly easier to read?)

[Rudy Dakota]

Hi guys,

Not based on any practical experience, but reasoning purely theoretical: using bind variables would make it impossible to take the detailed distribution of values over a given column into account when making the execution plan. The latter being one of the main advantages claimed for Oracle's Cost Based Optimizer.

I think you 'll find an advantage in using bind variables in the lion's share of cases anyway. It 's high on my list of interesting point to investigate without a business justification, nevertheless.

Good riding,

Rudy.

[Loren Rosen]

I suppose if you've got very unevenly distributed data, there could be some advantage to making a new query plan every time. But more usually there's greater gain to be had by re-using old query plans, and you get more re-use if you use bind variables.

[Carl Trusiak]

I have heard the hupla about the performance of PreparedStatements over Statements but, have never seen any benchmarks showing the performance enhancements. If someone has documents they can point me at, it would be appreciated. My own limited tests against an Oracle database showed virtually no difference in the times between the two.

However, having said that, PreparedStatements make some things easier, such as removing the need to escape special characters in Varchar(2) data types, no need to convert Dates and TimeStamps to Strings and use a Database fucntion to convert it in the sql, the ability to insert and update Binary data easily.

Reading PreparedStatements raw can be tricky, it is easy to have the ? run together. However if the PreparedStatement is logged after all the bind variables are set, it is fairly easy to read depending on the JDBC Driver. Oracles puts [<position number>, <value>] beside each ?

[Bear Bibeault]

I use PreparedStatement almost exclusively, and always when there are parameters to bind.

[Mark Spritzler]

Ht ereason why you should use bind variables is because they will be faster. Here is why.

When Oracle receives a query, it looks in the Library Cache in the SGA, which is the in-residence memory. It tries to find and exact copy, if it does than it can use the already made up explain plan. If it does not find and exact copy, it will have to parse the query and create an explain plan, which takes a small amount of time, this is called a hard parse. An Exact copy means exact, even in the count of spaces.

By using Bind variables, Oracle can keep that in memory and use the explain plan everytime. So whatever values you use in the bind variables, it will also use that explain plan without having to do a hard parse.

In Thomas Kyte's books "Expert one-on-one" he specifically states that you should use bind variables all the time. Mr. Kyte is a VP at Oracle, and the answer guy at Oracle's Ask Tom website, which is a must "Favorite" in any database/query person using Oracle. He has all the answers.

Good luck

Mark

[Chris Mathews]

From a developer perspective PreparedStatements are much nicer and make portability between databases that much easier since the JDBC driver takes care of character escaping, date formatting, and other such mundane details. So even ignoring performance characteristics, it makes a whole lot of sense to go with PreparedStatements. Like Bear, I use them exclusively at those times that have to drop down and write JDBC code (which occurs less and less frequently).

[Tim Holloway]

How much performance boost you get from PreparedStatements depends on the DBMS and driver. I've seen some systems where all the PreparedStatement handler did was convert the incoming data into a plain old SQL internally, but that the other extreme, you might get all sorts of optimizations. Mileage may vary depending on driver (urp!) and road conditions.

Like Mark said, the extra portability and elimination of the need to throw together Yet Another Set of SQL-Escape Utilities is worth something in and of itself.

Of course, the more intense the optimization is, the move overhead setting up a PreparedStatement can be. I've seen a particular problem in webapps, where I'd prepare a statement, use it in a request, then have to throw it away because I returned the connection it was based on back to the pool.

Lately, I've been playing with JDO. One of the neat things about the Kodo implementation (hey, do I get "plug money" for that? ) is that they CACHE their prepared statements and the connections are managed external to the in-and-out-flow of the web requests, which can potentially make things flow more efficiently.

Bottom line: Do your homework and see which tools match your project's needs. And, of course, keep things abstract in case you need to tune later!

[Chris Mathews]

Originally posted by Tim Holloway:
Lately, I've been playing with JDO. One of the neat things about the Kodo implementation (hey, do I get "plug money" for that? ) is that they CACHE their prepared statements and the connections are managed external to the in-and-out-flow of the web requests, which can potentially make things flow more efficiently.

Actually, many Application Servers and other Connection Pooling products do this as well. See this thread for a recent discussion.

[paul wheaton]

What about when things go wrong? An exception is thrown. Currently, the SQL is dumped and you can try the SQL in a SQL client.

Could this be a case of oracle optimization (and do you really gain that much here? It seems that the lookup and the network call would far outweigh whatever you gained here) vs. error handling readability?

[Jim Yingst]

What about when things go wrong? An exception is thrown. Currently, the SQL is dumped and you can try the SQL in a SQL client.

Naked PreparedStatements are generally harder to debug. JavaWorld addressed this problem in an article awhile ago, wrapping the PreparedStatement in a decorator to provide additional debugging info. Seems like their approach could be applied in Jenny.

Could this be a case of oracle optimization (and do you really gain that much here? It seems that the lookup and the network call would far outweigh whatever you gained here) vs. error handling readability?

I don't really know (and would be interested in hearing from anyone who does know). But I'd think that the lookup at least could be a relatively simple hash table lookup. The network call seems the more significant factor here. So I guess the question is, how long does it take to build a query plan (compared to network overhead)? Dunno.
[ May 27, 2004: Message edited by: Jim Yingst ]

[Chris Mathews]

Originally posted by Jim Yingst:
Naked PreparedStatements are generally harder to debug. JavaWorld addressed this problem in an article awhile ago, wrapping the PreparedStatement in a decorator to provide additional debugging info. Seems like their approach could be applied in Jenny.

I actually implemented this using Dynamic Proxies (so it would work with any version of JDBC) if anyone is interested... maybe something we could add to our JavaRanch class library.

[Marilyn de Queiroz]

Is your stuff available, Chris?

[Dave Churchville]

Pros and cons of using "bind variables" (PreparedStatements in JDBC):

When executing a PreparedStatement, drivers will usually "prepare" the statement itself first (either by making a server call, or within the driver), then pass just the data in subsequent calls, already encoded in the proper datatype. Normal SQL statements must be repeatedly parsed, and the data types converted on each call.

So if the same call is going to be made repreatedly, the PrepardeStatement should be faster.

In practice when using connection pooling you can't actually reuse a prepared statement, since you're not keeping connections open, etc. for more than one request. However, in app server containers (Weblogic, for example) there are options for caching prepared statements at the container level, so this adds back the performance gains.

If for no other reason, as stated above, it does free you from all of the syntactic issues associated with creating valid SQL, escaping quotes, etc.

It's a good investment, but you will need to account for database and driver differences. For example, if you're trying to bind decimal values, bits, booleans, etc, Oracle drivers might behave slightly diffrently to SQL Server drivers. Unfortunately that's the complexity that you were trying to avoid in the first place (or at least hide from developers).

This is why some people are using Hibernate, Toplink, EJB CMP and other ORM solutions, because they have already accounted for variations in SQL syntax, datatypes, etc.

So my advice is to try it out on a few databases before commiting to the internal implementation choice. Watch out for "odd" datatypes like big decimal, byte, bit, and boolean. I assume BLOB support is out of scope for Jenny.