db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Need stored procedure help!
Date Thu, 16 Dec 2010 10:00:41 GMT
sivagururaja <sivagururaja@gmail.com> writes:

> Even though i am using PreparedStatement in my procedure, i am initializing
> the PreparedStatement again and again by calling executeUpdate(). Which
> means it's equivalent to use the java.sql.Statement. If 'm not wrong, how
> come we achieve the performance. 

The use of a PreparedStatement in the procedure looks fine to me. Even
though you need to initialize the PreparedStatement on every call, the
statement will only be compiled the first time. On subsequent calls,
prepareStatement() will find the previous compiled execution plan in an
internal cache and short-circuit the compilation.

It can only do this if the SQL text in the statement is identical to the
SQL text in a previously compiled statement. If you had used a
java.sql.Statement instead, the SQL text would have changed between each
invocation (because you'd have a unique SQL text for each unique name
you try to insert).

Also, using a PreparedStatement with parameter markers saves you the
work to escape the input strings (http://xkcd.com/327/), which you'd
have to do manually with a Statement.

> I have two options. I have to pass the names Object to my procedure and
> insert all the names by iterating the same or I can use the above for-each
> loop.
> Now can you please tell me, which one you prefer in terms of efficiency.

I don't know. That would probably depend on your environment, if you run
with the embedded driver or the client driver, etc. But since you have
the code for both approaches, it should be easy enough to print a
timestamp before and after the loop and see which approach is faster in
your environment.

Knut Anders

View raw message