question about using PreparedStatement.
I have a code about PreparedStatement.
Connection con = fromDB;
PreparedStatement pstmt = con.preparedStatement("update table set name ='test' where id = ?");
pstmt.setInt(1, 22);
pstmt.execute();
pstmt.close();
con.close();
con = fromDB;
PreparedStatement pstmt = con.preparedStatement("update table set name ='test' where id = ?");
pstmt.setInt(1, 22);
pstmt.execute();
pstmt.close();
con.close();
if I reuse the same query after closing and reopening the db connection, I am just wondering if the PreparedStatement object still holds the compiled version of the same sql when it is used at the second time.
could somebody clarify this?
Thanks
If you close and reopen the DB connection, you cannot possibly have the same PreparedStatement object (even if you have the same variable name refering to it) , so no.
If you want to "parse once; execute many" then do:
// set up PreparedStatement
Connection con = fromDB;
PreparedStatement pstmt = con.preparedStatement("update table set name ='test' where id = ?");
// execute Preparestatement once
pstmt.setInt(1, 22);
pstmt.execute();
// and again (if the value doesn't change you don't need to re-bind, unless you called clearParameters() )
pstmt.setInt(1, 22);
pstmt.execute();
// cleanup
pstmt.close();
con.close();
Thanks for your clarification.
What about if code is executing in a j2ee container w/connection pooling? I've always been a bit confused about this. Some pseudocode:
someMethodThatUsesAPrepStatement(
try {
Connection c = ds.getConnection();
PreparedStatement ps = c.....
.... get data...
} catch {}
} finally {
ps.close();
c.close();
}
}
methodCallingOtherMethodOverAndOver () {
for (int i=0;i<100;i++) {
someMethodThatUsesAPrepStatement();
}
}
In this scenario, are there any efficiencies to be had by using a PreparedStatement? That is, does the connection object in the container-managed pool still retain the compiled ps? Or, since the ps is getting closed each time, does it have to compile it each time?
Anyway, anyone have suggestions for making a scenario like this efficient? REMEMBER-- I'm talking about code running in a J2EE container which threfore has connection pooling managed behind the scenes.... I'm *not* talking about a stand-alone java app which is much less murky to me...
thanks
There are 2 levels of efficiencies. The far far bigger one (at least on Oracle) occurs in the database and has nothing to do with Java and objects and open connections.
> There are 2 levels of efficiencies. The far far
> bigger one (at least on Oracle) occurs in the
> database and has nothing to do with Java and objects
> and open connections.
So...are you implying that the database caches the prepared sql and that because of that any connection that tries to use / prepare that same sql will get the performance benefit?
I've been under the impression that if the connection is closed then the database would have to re-prepare the sql a second time if a new connection tried to use that sql.
> So...are you implying that the database caches the prepared sql and that because of that any connection that tries to use / prepare that same sql will get the performance benefit?
Yes. There are 3 cases.
When Oracle recieves a DML SQL statement (insert/update/delete, etc), no matter how (Java w/JDBC), C++ w/ODBC, Oracle utility programs that get there other ways), it computes a hash from the SQL text and tries to look it up in a cache of already parsed SQL. This is case 1 and case 2.
case 1: If it doesn't find a match to the hash in the cache, it does all sorts of heavy-duty parse work, a little tiny bit of which is synchronised across the entire database, including all nodes in a RAC cluster. This is called a "hard parse". Keeping the number of hard parses down is critical to a succesful highly concurrent on-line transaction processing application. Of course, some hard parse can't be avoided - for one thing, Oracle's cache is of finite size and rarely used SQL will get pushed out. Also, for big time-consuming queries in a data warehouse environment, it may be the least of your concerns...
case 2: If it finds a match to the hash (and of other conditions are true, such as it's not a false hit), then Oracle reuses the stored execution plan. This is called a "soft parse". It still does some parse work unrelated to actual execution, but it's a lot lot less. Keeping the number of soft parses down is also good, but harder to do in some types of applications. In a lot of situations, java folks just don't worry too much about soft parses, or maybe use a driver or a connection pool that has built-in PeparedStatement caching.
case 3: If you have a PreparedStatement and bind different values to it, and execute it again; the SQL is NOT resubmitted to Oracle, and no parse occurs. Oracle just binds the new values to the existing structure it has. This is best and can be essential to DB-heavy applications such as batch processing jobs.
Now in Java, to avoid hard parses with SQL that contains frequently varying data values, you use PreparedStatement and the setXXX methods; that way the SQL text doesn't contain the data values, the hash of the text comes out the same despite changes to the data and Oracle finds the SQL in its cache.
> I've been under the impression that if the connection is closed then the database would have to re-prepare the sql a second time if a new connection tried to use that sql.
It does, but at least for Oracle, it tries to be clever and reuse what it can during a "soft parse".
