empire-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matthew Bond <b...@bond-it.de>
Subject AW: Prepared statement support?
Date Thu, 02 Dec 2010 09:19:33 GMT
Hi Rainer, Hi Kenji,

Rainer's comments are true in a Web Application scenario where the connection if got for a
short time and then released again. Empire DB can also be used in other scenarios, like a
Fat Clients or Command Line Utility tools, where a connection will probably be held for the
whole duration of the application  lifetime and PooledStatements could bring more performance.
So it really depends on what you application type you are programming.

FYI: WebSphere too pools prepared statements (see page 2 of http://www-03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcache.pdf
 "WebSphere, however, will do the caching automatically. When you execute a query, WebSphere
determines if the SQL text is already in the cache and if so, it will use that cached statement
instead of preparing a new one." ). So if EmpireDB was extended to make more use of Prepared
Statements it would be advantageous. 

However as Rainer describes,  the big benefit of using EmpireDB is that the selects are going
to be way better than other ORM's as the developer hand crafts the "SQL" statement.

The great thing is that it is Open Source so if you feel strongly about the use of PreparedStatements,
you could submit a Patch adding this functionality.


-----Ursprüngliche Nachricht-----
Von: Rainer Döbele [mailto:doebele@esteam.de] 
Gesendet: Donnerstag, 2. Dezember 2010 00:11
An: empire-db-user@incubator.apache.org; empire-db-dev@incubator.apache.org
Betreff: re: Prepared statement support?

Dear Kenji,

I have reviewed our code and thought about this subject again.
As you mentioned there is both a performance and a security issue to consider. 
For the moment I would like to focus on the performance issue as security can as well be established
by other measures.

It's pretty obvious to understand that creating a prepared statement and executing it multiple
times with varying parameters is superior over creating a normal statement each time. But
as far as I understand it, the advantage of a ps exists only as long as the statement lives,
and ends when you close it.   
The problem is, that a prepared statement is created for a particular connection. In a web-application
we usually use a connection pool and the connection is fetched for a particular request. It
is extremely rare, that the same statement is executed multiple times within a single request
- whereas it is very likely that the same statement needs to be executed by other users' requests.
As those other users have different connections they cannot share the same prepared statement.

Here is a thread discussing this issue:

As Empire-db does not store or maintain a connection, it is not sensible for us to store the
actual JDBC prepared statement object. But this might not be necessary as it could be done
on another level. Possibly the solution lies just in another Apache Project: Apache Commons

From my understanding it should be possible to use a commons-dbcp connection pool that will
also pool prepared statements. The connections returned by the pool can be used with Empire
db just like a normal JDBC connection.
Of course we still need to enforce and extend the generation of prepared statement phrases
beyond the CUD operations.

Still we must keep in mind, that probably for most real world applications the performance
benefit of prepared statements over simple statements is negligible, and it is our primary
goal to maintain simplicity and transparency.
It is IMO far more important to be able to create efficient statements - and avoid the problem
of OR-Mappers that usually work with lots of simple operations. After all, one clever statement
with server side db logic will still execute a lot faster than 10 prepared statements with
trailed Java logic.
(Still the gloal is to have it all of course)

Any more suggestions or remarks on this topic?


Kenji Nakamura wrote:
> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> to: empire-db-user@incubator.apache.org
> re Re: Prepared statement support?
> Rainer,
> Thank you for your reply. My comment are inline.
> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <doebele@esteam.de>
> 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: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
> >
View raw message