incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: Prepared statement support?
Date Tue, 30 Nov 2010 17:14:31 GMT
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.

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.

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.

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.

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.

Regards
Rainer


Kenji Nakamura wrote:
> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> to: empire-db-user@incubator.apache.org
> 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
Mime
View raw message