| How does sql prepare statement really work? [message #882972] |
Thu, 07 June 2012 15:02  |
 Gianluca Donati
Messages: 10
Registered: May 2012 |
Junior Member |
|
|
Hi,
the library created by wizard "Library from a database" in EDT 0.8.0 has several functions sharing the same datasource definition. If aTable is the table chosen during the wizard, the function getaTableListWithStatus receives listSpec, a parameter of ListSpecification type, and prepares a select statement.
If I call repeatedly this function changing the content of parameter listspec.fromTablesWhere, the function still prepares always the same statement!
I think this happens because the java code generated from EGL code:
selectStatement SQLStatement?{@SQLResultSetControl{scrollablity =
TYPE_SCROLL_INSENSITIVE}};
prepare selectStatement from dataSource with "SELECT * " + listSpec.fromTablesWhere;
looks like:
selectStatement =
(java.sql.PreparedStatement)dataSource.getStatement("my.package.library", 0);
if (selectStatement== null) {
String stmtStr = (("SELECT * ") + listSpec.fromTablesWhere);
selectStatement = dataSource.getConnection().prepareStatement(stmtStr,
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
dataSource.registerStatement("my.package.library", 0, selectStatement);
}
I understand that, for some reason, this code tries to get the statement from previous registered statements, and use my parameter only if there is nothing to get. But the EGL code doesn't suggest anything about this behaviour, nor have I found a way in EGL to force the use of my parameter, or clear the list of registered statements. And I bet that if I create another function in the same library with a complete different prepare statement from the same datasource, I will get always the same select...
Any idea? (except defining the datasource in each function: it works, because the datasource is created at any function call, but I wouldn't like it as a stable workaround, for code mantaining issues)
Gianluca
|
|
|
|
|
|
| Re: How does sql prepare statement really work? [message #886229 is a reply to message #886079] |
Thu, 14 June 2012 13:35  |
Matt Heitz
Messages: 36
Registered: July 2009 |
Member |
|
|
Hi Gianluca,
Yes, reusing a prepared statement can be much better than making a new statement. Your results will vary depending on the database and the SQL statement, but it's a good practice.
The database must do a lot of work when you make a new statement. First, the SQL string is parsed. Then references to tables, columns, etc. must be verified. The types of the columns and literals must be checked for compatibility. The database must verify that the user has permission to access and/or modify everything in the statement. And the database must compute the access path to the data, and determine how to return it.
When you make a prepared statement, most of that work is done only once. When you reuse the statement, the work doesn't need to be done again. In addition, some of the work that must be done every time can be done by the JDBC driver, which may eliminate extra communication with the database and reduce the load on the database server. Reusing the prepared statement might also mean that fewer objects are created, so there's less work for the JVM's garbage collector.
-Matt
|
|
|
|
Powered by
FUDForum. Page generated in 1.81968 seconds
] 
Search
Help
Register
Login
Home
