geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Hogstrom <m...@hogstrom.org>
Subject Re: AW: AW: Geronimo CMP update statements
Date Sun, 29 Jan 2006 00:52:38 GMT


Aaron Mulder wrote:
> We've used CMP a fair amount before at work, and IMO there's no reason
> it can't be made to perform well for most cases (you know, not
> necessarily for dynamic queries or updating 1000s of rows at once). 
> But I have to say, the SQL generation in Geronimo is something I've
> never seen before, and it's a litte more cumbersome than I'd expect. 
> I think we should offer 2 options: one option to update everything
> except the primary keys on every update (the "always use same
> statment" option where it just says update x set foo=?, bar=?, baz=?,
> etc.),

WebSphere uses that strategy.  The database optimizes access and does a pretty 
good job with the single prepared statement.  The side effect I've seen is that 
a lot of garbage is created along the way.  As a consequence, you end up putting 
additional stress on the processor cache.  For the old Intel DP line this is not 
good for Java performance but is less noticable on the Xeon line with larger L3 
caches.  Bottom line, it works and is simple.

> and another option to only update the fields that were changed
> on every update (may cause different SQLs for each update, but avoids
> problems with triggers and so on).  

BEA uses this strategy and it is very effective.  However, the sideeffect is 
that it does increase your JDBC statement cache.  So in cases where you have 
large numbers of updates and colums this can blow your cache out.

> There can be a flag in the
> deployment plan to say which strategy to use for each EJB.  

This should be done.  Agree.

> I'm also
> not convinced there's a huge advantage to separate syntax for separate
> RDBMSs when very plain SQL with the 2 options above should work the
> same for nearly everything.
> 

Speaking for DB2 there is a distinct advantage as DB2 supports several isolation 
levels and as such one can add specific predicates to the SQL to optimize the 
SELECT that is being performance.  This is more for query processing rather than 
update but can be a significant performance booster.

> I guess the only way to know for sure is to benchmark it.  But my
> recollection from our existing numbers is that our current CMP
> strategy is not really that high-performance anyway.

Ummm...yup

> 
> Thanks,
>     Aaron
> 
> On 1/28/06, Matt Hogstrom <matt@hogstrom.org> wrote:
> 
>>Michael,
>>
>>IMO the current SQL generation is not consistent with what I've seen in other
>>AppServers.  I don't have any specific data yet but I know for at least DB2 the
>>DB2 developers I've talked to have not seen this particular pattern.  As a
>>consequence, I expect Oracle would probably have the same comment.  As it stands
>>right now I think we need to go back and invest some serious time rewriting the
>>SyntaxGenerators to generate more efficient SQL (at least for DB2).
>>
>>One of the feedback items I've heard is that CMP is not that prevalent in the
>>world in terms of adoption.  I'd be curious to get your feedback on how you use
>>CMPs and their ubiquity in your environment.
>>
>>Thanks
>>
>>Matt
>>
>>Ueberbach, Michael wrote:
>>
>>>Hello Matt,
>>>
>>>yes, this works. Thanks for the hint.
>>>I think geronimo sends a statement like this one to the database
>>>
>>>UPDATE table SET col1 = CASE WHEN false THEN null ELSE col1 END, col2 = CASE WHEN
true THEN newValue ELSE col1 END, ...
>>>
>>>and Oracle does not know how to handle the boolean values true and false, so they
have to be replaced by some expressions like 1=1 or 1=0. (This way I can reproduce the situation)
>>>
>>>Nevertheless I think this behaviour should be modified not only for the reason
of wrongly fired triggers but also to reduce the transportation load.
>>>
>>>regards
>>>Michael
>>>
>>>
>>>
>>>-----Urspr√ľngliche Nachricht-----
>>>Von: Matt Hogstrom [mailto:matt@hogstrom.org]
>>>Gesendet: Donnerstag, 26. Januar 2006 17:22
>>>An: user@geronimo.apache.org
>>>Betreff: Re: AW: Geronimo CMP update statements
>>>
>>>
>>>Michael,
>>>
>>>Add the following lines in your plan:
>>>
>>><!--  For Oracle database users uncomment the following line.
>>>
>>><ejb-ql-compiler-factory>org.tranql.ejbqlcompiler.OracleQLCompilerFactory</ejb-ql-compiler-factory>
>>>
>>><db-syntax-factory>org.tranql.sql.oracle.OracleDBSyntaxFactory</db-syntax-factory>
>>>-->
>>>
>>>These go after the cmp-connection factory.  Let me know if this fixes the problem.
>>>
>>>Ueberbach, Michael wrote:
>>>
>>>
>>>>Hello,
>>>>
>>>>I think there is another problem concerning this issue.
>>>>Using an Oracle database (9i) I get this error when updating a cmr- field:
>>>>
>>>>(...)
>>>>Caused by: org.tranql.ql.QueryException: Error executing statement: UPDATE
konto SET erzeugt = CASE WHEN ? THEN ? ELSE erzeugt END, bankname = CASE WHEN ? THEN ? ELSE
bankname END, bankleitzahl = CASE WHEN ? THEN ? ELSE bankleitzahl END, kontonummer = CASE
WHEN ? THEN ? ELSE kontonummer END, kontostand = CASE WHEN ? THEN ? ELSE kontostand END, fk_person
= CASE WHEN ? THEN ? ELSE fk_person END WHERE guid = ?
>>>>(...)
>>>>Caused by: java.sql.SQLException: ORA-00920: invalid relational operator
>>>>
>>>>This not the case when using MySQL.
>>>>
>>>>regards
>>>>Michael
>>>>
>>>>
>>>>-----Urspr√ľngliche Nachricht-----
>>>>Von: Gianny Damour [mailto:gianny.damour@optusnet.com.au]
>>>>Gesendet: Donnerstag, 26. Januar 2006 13:16
>>>>An: user@geronimo.apache.org
>>>>Betreff: Re: Geronimo CMP update statements
>>>>
>>>>
>>>>Hi Dan,
>>>>
>>>>At the beginning, I was not seeing this as an issue. Based on your
>>>>remark that triggers are wrongly fired, I now see this as an issue that
>>>>needs to be fixed.
>>>>
>>>>Could you please raise a JIRA for this problem?
>>>>
>>>>Thanks,
>>>>Gianny
>>>>
>>>>Daniel John Debrunner wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>At ApacheConUS 2005 I talked with Matt Hogstrom about the SQL UPDATE
>>>>>statements Geronimo was issuing against Derby for DayTrader.
>>>>>
>>>>>A single UPDATE statement is generated for a table that updates all
>>>>>columns using a CASE statement to ensure un-modified columns are not
>>>>>changed, or in reality changed to the same value. An example is
>>>>>described in GERONIMO-1080, the syntax may be a little different for Derby.
>>>>>
>>>>>http://issues.apache.org/jira/browse/GERONIMO-1080
>>>>>
>>>>>I see two issues with this approach, one is that for Derby this is
>>>>>inefficient, and two, and probably more important, all SQL update
>>>>>triggers will fire due to this modification of all columns. Thus if an
>>>>>application defines a trigger on update of the address column of a
>>>>>customer table, then when using Geronimo this trigger will fire, even
if
>>>>>the CMP application is only updating the customer's balance. This just
>>>>>seems the wrong semantics to me.
>>>>>
>>>>>Matt had said this was a known issue, and that it was going to be fixed.
>>>>>The comments in GERONIMO-1080 seem to indicate that this may not be seen
>>>>>as an issue, though those comments are dated before ApacheCon.
>>>>>
>>>>>I searched Jira and couldn't see any bug for changing this, are there
>>>>>any plans to address this?
>>>>>
>>>>>Thanks,
>>>>>Dan.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
> 
> 
> 

Mime
View raw message