Re: bind variables from java code

hayt_at_inwind.it (hayt) wrote in message news:<11f54ca0.0305270110.487fb238_at_posting.google.com>...
> Yes, I know: you are right. Of course is faster if I reuse all times a
> compiled-once statement. But in my case I cannot reuse the same
> statement, because the SP calls are performed through a connection
> pool, and I cannot release a pooled connection without close the
> statement.

If what you want to do is "Connect -> Compile Statement -> Execute -> Close Statement", then this whole discussion is moot. Oracle by design will cache (if it can) Prepared and Callable Statements so no matter how you call your statement on the JDBC side (as long as you compile each time you call), performance difference will be minimum to none. However, if you can cache previously used CallableStatement in your connection pool and rewrap and reuse in the next call, then you will gain performance on both sides. One could say that it may be expensive to cache Statements in the connection pool BUT compared with the performance gain... I guess you would have to look at it on Statement by Statement basis.

> By the way, my question remains: why is (a bit) faster the
> compilation/execution if a stored procedure when the variables are
> embedded in the string?

It depends on the driver. Some drivers send the statement to the database when prepareCall is called, some when CallableStatement is executed. The performance difference is minimum when compiled and called each time... as mentioned above. Plus you also have to figure in the overhead of registring all IN/OUT parameters each time. Maybe you can test and note the execution time if the Variable values are changed in each call... (Since in your original loop test... hard coded values remain the same).

> But if I replace the CallableStatement with a PreparedStatement, how
> can I get the SP output parameters?

I just showed it as an example. Only CallableStatement has OUT parameter registration.

Regards
/Rauf Sarwar Received on Tue May 27 2003 - 21:40:12 CEST