aspose file tools*
The moose likes JDBC and the fly likes Prepared statement - static variable Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statement - static variable" Watch "Prepared statement - static variable" New topic
Author

Prepared statement - static variable

Babji Reddy
Ranch Hand

Joined: Jan 24, 2006
Posts: 106
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

Joined: Jan 25, 2002
Posts: 775
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 ]

Reid - SCJP2 (April 2002)
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31069
    
232

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
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

Joined: Jan 24, 2006
Posts: 106
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

Joined: Jan 24, 2006
Posts: 106
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

Joined: Jan 25, 2002
Posts: 775
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

Joined: Jan 24, 2006
Posts: 106
Point taken..
Thanks a lot
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333



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

Joined: Jan 25, 2002
Posts: 775
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared statement - static variable