incubator-empire-db-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: All "prepared" now!
Date Fri, 03 Dec 2010 10:25:25 GMT
Hi Francis,

Yes indeed a hard night.
I had a problem with the parameter order that just took me some time to fix.

I have seen that I missed a few places that sill generate simple statements.
Will submit another patch today after a few more tests.

Yes it's a shame that this isn't part of the release - I think it is an important major feature.

How about stopping the current rc and going for the next one?
I would probably safe us some unnecessary double work.

Since I had to change a few things in the interface of DBCommand I would like to call the
next release 2.1.0.

What do you think?

Regards
Rainer


Francis De Brabandere wrote:
> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> to: empire-db-dev@incubator.apache.org
> re: Re: All "prepared" now!
> 
> Busy night Rainer? Great to hear this is implemented :-) Too bad we
> just cut a release :-s
> 
> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <doebele@esteam.de> wrote:
> > Hi Kenji,
> >
> > good news for you: it's all "prepared" now :-)
> > Most of it was already there, but a few bits and pieces were missing.
> > However you have to take the latest sources directly from our SVN
> repository.
> >
> > In DBDatabase you now have a property called
> "preparedStatementsEnabled" which you can enable or disable.
> > If enabled Empire-db will use prepared statements for all database
> operations on DBRecord.
> >
> > If you create your own commands using DBCommand you will have to
> explicitly declare the parameters using DBCommand.addCmdParam() which
> will return a DBCommandParam (I have renamed this inner class from
> DBCmdParameter!) except for set() operations.
> >
> > I have added an example to the empire-db-example-advanced
> (SampleAdvApp.java). The commandParamsSample() method shows how to use
> command params.
> > By additionally setting preparedStatementsEnabled to enabled, also all
> operations performed by Empire-db itself will use prepared statements.
> > You can easily see it in the log.
> > And the good thing is, you can easily switch between prepared and non-
> prepared (non-prepared statements are still better for debugging).
> >
> > Let me know, if you have problems or need any more help.
> > Best regards,
> >
> > Rainer
> >
> >
> > Kenji Nakamura wrote:
> >> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> to: empire-db-user@incubator.apache.org
> >> re: Re: Prepared statement support?
> >>
> >> Hi Rainer,
> >>
> >> Yes, this is exactly what I was looking for.
> >> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
> think
> >> it is a job of connection pool utility.
> >> We use c3p0 and it has statement pooling capability. It is highly
> >> configurable and has lots of features.
> >>
> >>
> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
> >> oling
> >>
> >> I really appreciate if you can include the bug fix of DBReader in the
> >> next release as this is crucial feature to persuade DBAs and security
> >> auditors.
> >>
> >> Thanks a lot!
> >>
> >> Kenji Nakamura
> >>
> >> On Dec 2, 2010, at 19:29, Rainer Döbele <doebele@esteam.de> wrote:
> >>
> >> > Hi everyone,
> >> >
> >> > thanks for your comment Matt.
> >> >
> >> > To my own surprise I have overlooked that there is already
> substantial
> >> support for prepared statement generation in Empire-db now, but you
> have
> >> to explicitly declare the parameters.
> >> > Here is an example of how to generate a prepared statement phrase
> and
> >> execute it with the corresponding parameters:
> >> >
> >> >    // Define the query
> >> >    DBCommand cmd = db.createCommand();
> >> >
> >> >    // Create parameters
> >> >    DBCmdParameter depIdParam  = cmd.addCmdParam(1);
> >> >    DBCmdParameter genderParam = cmd.addCmdParam('F');
> >> >
> >> >    // create statement
> >> >    cmd.select(EMP.getColumns());
> >> >    cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
> >> >    cmd.where(EMP.GENDER.is(genderParam));
> >> >
> >> >    // First execution
> >> >    String sql = cmd.getSelect();
> >> >    ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
> >> conn);
> >> >    // do something
> >> >    r.close();
> >> >
> >> >    // Modify command parameters
> >> >    depIdParam.setValue(2);
> >> >    genderParam.setValue('M');
> >> >
> >> >    // Second execution
> >> >    r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
> >> >    // do something
> >> >    r.close();
> >> >
> >> > This will result in the following SQL:
> >> >
> >> >    SELECT t2.EMPLOYEE_ID, t2...
> >> >    FROM EMPLOYEES t2
> >> >    WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
> >> >
> >> > And set the parameter to 1 and 'F' for the first query and to 2 and
> >> 'M' for the second.
> >> >
> >> > Unfortunately there is a bug in DBReader so that cmd params are not
> >> properly set.
> >> > This is the reason why I used db.executeQuery(..) instead of a
> >> DBReader in the example above.
> >> > I will fix this bug as soon as possible.
> >> >
> >> > Another thing we should do is to use the prepared statements for
> >> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
> >> >
> >> > As far as the pooling of prepared statements is concerned, if it's
> not
> >> done by the data source already it can also be done by subclassing
> the
> >> DBDatabaseDriver and overriding executeQuery() and / or executeSQL()
> and
> >> do it yourself. But it is not necessary for Empire-db to provide
> this.
> >> >
> >> > Kenji will this satisfy your needs?
> >> >
> >> > Regards,
> >> > Rainer
> >> >
> >> >
> >> >
> >> > Matthew Bond wrote:
> >> >> from: Matthew Bond [mailto:bond@bond-it.de]
> >> >> to: empire-db-dev@incubator.apache.org; empire-db-
> >> >> re: AW: Prepared statement support?
> >> >>
> >> >> 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_stmntcach
> >> >> e.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.
> >> >>
> >> >> Cheers
> >> >> Matt
> >> >>
> >> >> -----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:
> >> >> http://www.velocityreviews.com/forums/t644638-jdbc-
> preparedstatement-
> >> in-
> >> >> a-multi-threaded-environment.html
> >> >>
> >> >> 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
> >> DBCP.
> >> >> http://commons.apache.org/dbcp/index.html
> >> >>
> >> >> 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?
> >> >>
> >> >> Regards
> >> >> Rainer
> >> >>
> >> >>
> >> >> 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
> >> >>>>
> >
> 
> 
> 
> --
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.

Mime
View raw message