Statement and Resultset

[Chris Uppal]

I will need to re-think on how I use Statement vs PreparedStatement,
though at this point in the application we are talking about many
thousands of places. It would mean major re-factoring and subsequent
regression testing.

I wouldn't dream of suggesting that you change your current system (which, in
fact, sounds very well thought out).

-- chris

 

[dnass]

Hi all,
I've a java method (included in a java class) like this above:

public static ResultSet eseguiSelect(Connection conn,String query) throws
SQLException
{
LogTrace log = new LogTrace();
log.trace("query : "+query);
Statement stmt = null;
ResultSet rs=null;
try
{
stmt=conn.createStatement();
rs=stmt.executeQuery(query);
}
catch(SQLException sqle)
{
log.trace("SQLException : "+sqle.getMessage());
log.trace("SQLState : "+sqle.getSQLState());
log.trace("VendorError : "+sqle.getErrorCode());
try
{
rs.close();
stmt.close();
}
catch(Exception exception)
{ }
throw sqle;
}
return rs;
}

In this execution I don't close "stmt" and "rs" 'cause "rs" must be returned
to caller of method; but this can be dangerous? i.e. this statement and
resultset unclosed could remain active with consequent memory occupation?
Thanks in advance
JFM

Hello all,

Why wouldn't you use CachedRowSet instead ?
here is a tutorial
http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html

DNas

 

[buggy]

Lew wrote :


In fact, the DB side could be completely unable to tell the difference,
since the JDBC layer could hide it.

Do a Google search on: sql prepared statement -java -jdbc

The first return was an IBM DB/2 manual
http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/r0000975.htm

About 2/3rds way down the page is this statement:

"A prepared statement can be executed many times. Indeed, if a prepared
statement is not executed more than once and does not contain parameter
markers, it is more efficient to use the EXECUTE IMMEDIATE statement
rather than the PREPARE and EXECUTE statements."

Another entry was the PostrgeSQL at address
http://www.postgresql.org/docs/current/static/sql-prepare.html

where it states in the Notes:

"In some situations, the query plan produced for a prepared statement
will be inferior to the query plan that would have been chosen if the
statement had been submitted and executed normally. This is because
when the statement is planned and the planner attempts to determine the
optimal query plan, the actual values of any parameters specified in
the statement are unavailable. PostgreSQL collects statistics on the
distribution of data in the table, and can use constant values in a
statement to make guesses about the likely result of executing the
statement. Since this data is unavailable when planning prepared
statements with parameters, the chosen plan may be suboptimal."

For Microsoft at address
http://support.microsoft.com/kb/224587

it states
"Ideally, an application must prepare a SQL statement one time and run
it several times. This saves the Optimizer the cost of compiling a new
plan each time the statement is executed. Therefore, the number of Exec
Prepared SQL events should be much larger than the number of Prepare
SQL events. If the number of Prepare SQL events is roughly equivalent
to the number of Exec Prepared SQLevents, this may indicate that the
application is not making good use of the prepare/execute model. It is
best not to prepare a statement that is only going to be executed a
single time."

So it seems that the DB engine does more work, rather than JDBC. And
that the PreparedStatement may be less efficient than a Statement.

If the underlying DB does not support PreparedStatement, then of course
the JDBC driver should implement it. Of course it must then also handle
any special characters as per Chris post.

 

[Guest]

I see your point, but I just do not see how a programmer can make *any*
performance decisions about code to which the only access is an
interface.

I can repeat it once more: THE NAME.

Furthermore, others on this thread have offered anecdotal
evidence that the naive expectation is, in fact, violated in the real
world.

Well - I scanned through the thread - and I did not see such.

Furthermore, the "clear indication" says absolutely nothing about
performance, only usage structure.

Wrong. PreparedStatement gives a clear indication of that it is expected
to use a SQL prepared statement if the database supports such and
that gives an indication of performance - and that is both with and
without that support.

Arne