geronimo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: CMP FBPK Yields an individual SELECT per column
Date Thu, 08 Sep 2005 18:19:33 GMT
Matt asked a couple of questions on IRC related to this:

 > SELECT Q.symbol FROM QuoteEJB Q WHERE Q.symbol = s:153

This query is checking whether a entity exists - I thought for that we 
actually issued
SELECT 1 FROM QuoteEJB Q WHERE Q.symbol = s:153
but the effect is similar.

The columns are probably being fetched individually because there is no 
pre-fetch information defined. The challenge here is to pick the right 
default set for the work that is being performed in the transaction - 
not too much, not too little.

If we simply fetch all, then we may load way more information than is 
needed and that gets problematic when the table has a couple hundred 
columns or large objects like attachments or images.

If we fetch too little then we see this behaviour and do way more trips 
to the database than desirable.

Complicating things further is that there is often little correlation 
between the finder invoked and the fields that get accessed during the 
transaction. For example, when an application is displaying data, it may 
find a bean and then need to read every field so adding all fields to 
the query is desirable; when updating data, it may use the same finder 
but then overwrite every field and so optimal behaviour would be to add 
no fields to the query.

Also, a lot of this behaviour is influenced by the caching strategy in 
place. This lack of prefetch information does not cause problems if all 
the data being accessed is held in a local cache; it will be faulted in 
once and then reused. Of course, if caching is disabled then this won't 
perform well.

Where the appropriate hook points are also depends on the front-end 
persistence model. For example:
* CMP1 allows access to fields and has no relationships so you really
   need to load all fields for the bean and can't prefetch children
* Hibernate allows access to fields but does have relationships so
   you need to load all fields but can prefetch (this is without field
   access interception, if you do that you can lazy load fields)
* CMP2 intercepts all access so you can choose which fields to load
   and can prefetch relationships

TranQL supports all these different models through the concept of query 
events (e.g. when a finder runs) and though cache-miss events. The basic 
strategy is "do-something-when-a-cache-miss-occurs" where "something" is 
defined by the front-end depending on the access model it supports; the 
"something" may have side effects such as loading other values into the 
cache (which is how prefetch works).

So, the simple fix here is to set up prefetch associated with the finder 
or with the ejbLoad event which will load all the columns for the bean.

In the longer term, OpenEJB should be extended to associate cache 
pre-load operations with transaction initiation so that the entire data 
graph can be loaded up front in one query.

--
Jeremy

Matt Hogstrom (JIRA) wrote:
> CMP FBPK Yields an individual SELECT per column
> -----------------------------------------------
> 
>          Key: GERONIMO-985
>          URL: http://issues.apache.org/jira/browse/GERONIMO-985
>      Project: Geronimo
>         Type: Bug
>     Versions: 1.0-M5    
>  Environment: Geronimo w/Derby
>     Reporter: Matt Hogstrom
> 
> 
> I'm testing the DayTrader Application and it appears that FBPK finds are executing a
single SELECT per field in the CMP field.  Here are the SELECTs making up a single OrderEJB.findByPrimaryKey().
 This should be broken down into a single SELECT for the entity.
> 
> SELECT Q.symbol FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.symbol FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.companyName FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.volume FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.price FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.open1 FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.low FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.high FROM QuoteEJB Q WHERE Q.symbol = s:153
> SELECT Q.change1 FROM QuoteEJB Q WHERE Q.symbol = s:153
> 
> 
> 
> 


Mime
View raw message