Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared statement - static variable

 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does making a prepared statement variable as static will yield any performance improvements? Or it violates its functional purpose itself?
For eg.,
final String s = "select a,b from c where d=?" ;
static PreparedStatement prepSt = null ;
....
if( prepSt !=null )
{
prepSt = conn.prepareStatement(s) ;
}
s.setString(1,"e");
s.executeQuery().

Thanks in advance
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think what you are looking for is caching of prepared statements. Some connection pools do that for you as an optional tuning parameter (Weblogic, I think JBoss too). Handy for situations where the same prepared statement will get used multiple times in a runtime session, not necessarily even in the same transaction. Your use of a static basically means you only think you'll have one of them instead of needing a cache for multiple statements, so theoretically it would work. The thing I'm not sure of is if the prepared statement cache can be shared across connections, or if it is connection-specific; if the latter, your static may break if you use a connection pool.
[ January 30, 2006: Message edited by: Reid M. Pinchback ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34680
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PreparedStatements are cached separately for each connection. They are also cached by string value rather than by object. So it is unnecessary to make it static and (as Reid pointed out) could cause other problems.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jeanne Boyarsky:
They are also cached by string value rather than by object.


The situation may be a bit more complex than just caching the string for the SQL, although the SQL string would be the obvious key for doing a cache lookup. Drivers may go through a process of analyzing the bind items for a prepared statement. That is actually the useful bit that gets cached.

If there weren't something of the sort going on, there would be no difference in overhead between regular (dynamic SQL) statements and prepared statements. It has been a few years since I timed the difference, but it used to be the case for Oracle drivers that creating a prepared statement cost 3x or 4x that of a regular statement. You can also tell there is some kind of performance benefit from what is cached by doing comparative timings in Weblogic, which I have done in the last year. Depending on your prepared statement cache size and the type of CMP activity you have going on, you can see a 10-30% performance increase with an appropriately-sized PS cache. If only SQL strings were being cached, there would be no reason to see a performance change when you change the PS cache size.
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks..
That makes sense. If a statement is created with a different connection, then probably directly exeucting without using 'prepareStatement()' again may throw some exceptions. (I will test this behavior, and repost).
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got another doubt.
suppose we had to prepare a sql dynamically ( like reading the db schema name during runtime and 'prepend' it to the table name), in such cases since we construct the string during runtime, we can append bindings directly instead of making '?' and then use setxx().
Now, the question is 'since the sql string didn't have any ? to bind', will it make sense to designate that query as 'prepared statement' instead of just statement?
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes you can append values directly. Yes you can use either a regular Statement or a PreparedStatement, although if you take appending values to the point that you have absolutely no bind items I can't think of any reason to use a PS. The right course depends a lot on what you are doing.

The major downside of appending specific values is that the database will have to calculate an execution plan for almost every statement you submit for execution, even if they are essentially the same query, just different via some parameters. That is time consuming and causes execution plans to get rapidly flushed from their cache, which limits how many simultaneous query threads a database can handle (at least for Oracle that is definitely true).

There are other downsides too, like being forced to make sure to properly escape special characters in strings, dealing with date formats, etc. If all you are appending are simple values (integers, strings you know won't need escaping), it is pretty much up to you. There are some degenerate situations where appending specific values can actually be faster, but that isn't the norm. Appending to a SQL string in some situations ends up evolving into something you wish you never started.
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Point taken..
Thanks a lot
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



It has been a few years since I timed the difference, but it used to be the case for Oracle drivers that creating a prepared statement cost 3x or 4x that of a regular statement.


Actually, at least as far back as the 8.1.7 driver, the difference isn't nearly that high. However many people have ended up with false results in their casual timing tests because in the Oracle driver, getConnection will use a Statement internally. Folks then end up comparing the time between "execution time of Statement" versus "class load time + execution time of PreparedStatement"; class load time can be pretty significant.

A correctly run single-threaded timing test with (apparently) the 9i driver on a 9i database produced these results:
Rows to Insert Statement PrepareStatement
1 0.05 seconds 0.05 seconds
10 0.30 seconds 0.18 seconds
100 2.69 seconds 1.44 seconds
1000 28.25 seconds 15.25 seconds
http://asktom.oracle.com/pls/ask/f?p=4950:8:14323156943165993748::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:10128287191505
A muti-threaded test should show even more dramatic differences, especially with a database on a muti-CPU machine or RAC cluster.

Even very very rarely used SQL that has different data values should almost always be executed using PreparedStatement with data bindings; this is even more true in a RAC cluster. There are a very very very few exceptions to that rule of thumb though, involving data skew, where clauses, and indexes; even in those cases, you should use PreparedStatement, but then not bind the where clause value.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good to see current numbers for the differences; it had been awhile since I'd checked the specifics. I know when I did my tests I controlled for class loading and anything else I could think of. When you did these tests were you creating the prepared statement once and re-using it multiple times, or were you creating the prepared statement for every iteration? The numbers you show make me suspect the former. Benefiting from re-use of a PS obviously isn't a surprise, but I'd be surprised to see that even when you can't re-use the PS you still win on inserts to such an obvious degree. Good news if that was how you tested, but not what I would have guessed.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic