Rainer, Thank you for your reply. My comment are inline.
On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <[email protected]> wrote: > Hi Kenji, > > thanks for your interesting links about this subject. > > It is certainly true, that the performance of a prepared statements is better > when you execute it multiple times with varying parameter values. > This is not always possible when varying statements with conditional joins > are created at runtime. > For a one-time statement using a prepared statement does not execute faster > than a normal statement. I understand the issue that the use of PreparedStatement seems to have overhead and actually it may take longer if we measure it with a single execution from application developer's point of view, but the compiled result of the statement is kept added to Oracle's cache and it flushes the compiled results of the PreparedStatement invoked from different applications as the cache is managed per SID in Oracle. So it has negative impact from the DBA's point of view. It is not an issue as long as the DB is used as the data storage of a web application server and the performance of the app is only concern, but the assumption is not true when the DB is also used in data processing. > The inclusion of parameter values in the SQL text when assembling statements > is an advantage when it comes to logging (logging of parameterized statements > is not sufficient to track errors) or for the creation of SQL scripts that > are saved and executed later. I see your point. > > Currently Empire-db uses prepared statements by default only for statements > with BLOB and CLOB fields. > > However at least as far as update and insert statements are concerned you can > override the method useCmdParam() in DBCommandOracle, but you need to > subclass the DBDatabaseDriverOracle and override createCommand first. If you > return true in useCmdParam(), then Empire-DB will use a prepared statement > and supply this value as a prepared statement parameter. >From the point of view of Oracle administrator, the primary interest is how to reduce the # of hard parse and increase the hit rate of the cache, and using PreparedStatement only for CUD operation is not sufficient if the ratio of Select outweigh CUD operations. From security point of view, Select statement with parameters embedding user's input is as vulnerable as other DMLs, so the option to use PreparedStatement for CUD operation doesn't address those concerns, while it may be useful to improve the performance on iterative operations. > > Personally I have used Empire-DB in many projects and performance or security > have never been a problem. However, if you except to execute 10.000 sql > statements a minute then certainly this needs to be thoroughly checked. It is nice to know the framework has been proven in production environments. Our current performance test also doesn't show the hard parse is the primary culprit of the performance bottleneck, so it is not an urgent problem, but I'd like prepare to answer the questions from our DB engineers. > > I have created a new Jira (EMPIREDB-91) issue for us to check, how and where > we can increase and optimize the use of prepared statements. Thank you for the reaction. I registered myself to the watch list. Let me know if I can do something to make this forward. Lastly, I really thank you to share the framework in public. I have used Toplink, Hibernate, and iBatis, but I favor empire-db a lot because of the simplicity and type-safe coding. It is very straightforward to customize to fulfill our specific needs such as the support of TableFunction in Oracle. Regards, Kenji > > Regards > Rainer > > > Kenji Nakamura wrote: >> from: Kenji Nakamura [mailto:[email protected]] >> to: [email protected] >> re: Prepared statement support? >> >> Hi, >> >> I got a question from one of our DB engineer about the use of prepared >> statements. >> According to him, or a thread in AskTom, it is always preferred to use >> PreparedStatement instead of Statement whenever possible. >> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11_Q >> UESTION_ID:1993620575194 >> >> As far as I looked at the code, PreparedStatement is not used other >> than DBDatabaseDriver class and the method is not used from other >> code. >> >> My understanding is that creation of PreparedStatement has certain >> overhead, but statement pooling introduced in JDBC 3.0 mitigates the >> impact especially from application server point of view. >> We use Oracle, and the DB engineer explained that the use of statement >> floods the library cache in SGA and reduce the hit rate of >> pre-compiled statements so it has negative impact on entire db, and >> using PreparedStatement simply reduces the cost of hard parse. >> >> Another aspect is about SQL injection prevention. I noticed single >> quotes are escaped at DBDatabaseDriver#getValueString() method, but >> the preferred way to prevent SQL injection is to use PreparedStatement >> according to OWASP website. >> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet >> >> Would you tell me the design philosophy or reasons not to use or >> provide the option to use prepared statement? Is it possible, or have >> a plan to support PreparedStatement? >> >> Thanks, >> >> Kenji Nakamura >
