This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

Prepared Statements in a Loop

Jul 4th, 2005, 02:55 AM

Hi,

I know this is a bit of a newbie question but I could not find any clues on the net or in the reference guide. I want to set up prepared statements to do database queries but have a question on how/where in the code to do it.

I'll use the example from the reference guide to frame the question. Say I have an app that loops over 1000 user IDs.

For my DB query, I have a custom mapping query like what is shown in the reference guide:

So each of these 1000 times, I am instatiating a new CustomerMappingQuery. On the surface, it looks like this will define and compile a new prepared statement every time I new the query.

Is that the case? Or does Spring realize that I am trying to define and compile a statement that I have already defined last time in my loop.

I'm wondering if I get any real benefit or if I have to somehow define the statment at the beginning, then just call with parameters each time in the loop. Would I have to create all of the custom mapping queries in the custructor of my loop class??

Comment

From my prior Oracle/JDBC experience (but not Spring/Hibernate until recently).

The best way to use prepared statements is to define them once and only once. Each call to the prepared statement with params will then corectly use the bind variables (on Oracle).

If inside your loop you are creating a new prepared statement object, I'm pretty sure that at the DB level, you'll be generating unneccessary work. (ie ignoring bind variables - reparsing the whole statement instead of just re-execute with the new params, on Oracle at least, many performance problems stem from people forcing the DB to re-parse their statements).

How this translates through Spring/Hibernate I'm not exactly sure, but just looking at the code, I'd prefer to create the query only once and then just setParams and execute. Another thing to consider is the fact that you're creating many short-lived objects (custQry), these will be fairly large and will rapidly fill up the part of the heap reserved for short-lived objects (Eden?) and be pushed into the long-lived area of the VM. This will leave the VM short of space for the real short-lived objects (parms) and you'll have to wait for GC to collect all the old custQry objects

You may also get away with defining the query statically in a static initializer and then just calling it when appropriate. This depends on the nature of your app and how threaded etc it is, but I've seen massive performance improvements from careful use of this.

But with all things YMMV, caveat emptor etc

Kev

Comment

If inside your loop you are creating a new prepared statement object, I'm pretty sure that at the DB level, you'll be generating unneccessary work. (ie ignoring bind variables - reparsing the whole statement instead of just re-execute with the new params, on Oracle at least, many performance problems stem from people forcing the DB to re-parse their statements).

AFAIK if you create the same PreparedStatement again it can be handled by the database cache. For Oracle I know that if the statement is already in the cache, only the parameter binding is necessary.

Of course if one is able to reuse the same statement again, this should be preferred.