Re: [JBoss-user] Re: [ jboss-Bugs-532734 ] Ineffective SQL-code generated

[Dain S. <da...@da...>]

Marius Kotsbak wrote:

> On søn, 2002-04-14 at 16:59, Dain Sundstrom wrote:
> 
>>Marius Kotsbak wrote:
>>
>>
>>>It seems like the reason that the IN-clause is needed, is that jbosscmp
>>>is quering one table at a time. I understand that that might be the
>>>easiest way. 
>>>
>>
>>What?  The IN clause is not needed.  The current code works today.
>>
> I know. But when is in neccecary to query with a list of IDs? Caching?


Yes, we read-ahead (preload) additional entities.

>>>And is it required to support BMP (can CMP have
>>>relationship with BMP?)?
>>>
>>
>>No, cmp can't have a relationship with BMP.  The problem is each side 
>>needs to inform the other of certain events, and BMPs couldn't do that.
>>
>> 
>>
>>>Would it be hard/possible to combine the tables that are queried, with
>>>JOIN? I think that would speed up the CMP a lot. It would also reduce
>>>the number of DB-queries.
>>>
>>
>>What?  I do that with relationships, but there is no need to do this 
>>with a simple entity load (there is nothing to join with).
>>
> ok.
> 
> 
>>
>>>This should not be of high priority as it works as it is, but speed is
>>>also crucial for many apps.
>>>
>> >a
>>
>> > Marius K
>> >
>>
>>Speed how.  The only think I have had reported is some lame dbs take 
>>longer to parse the query below.
>>
> So is this kind of query neccecary? Since it probably is dynamically
> generated, wouldn't it benefit less from prepared statement
> (precompiling)?


Prepared statements are useful for setting parameters.  Actually they 
are the only cross platform way to set parameters.

 
>> I have written a compiler and I tell 
>>you there is no reason that the query below should take a long time.
>>
> Is there an easy way to enable logging of all sql-code that is actually
> executed, so I can see what happens and tune my code?


Yes it is in the server.log file.  It is logged at the DEBUG level of 
the org.jboss.ejb.plugins.cmp categories.  Look at the log4j.xml file 
for more info on logging.

 
> About this one from SF:
> 
> "You have tuned you application for the worst possible 
> preformance.  It is doing a readahead, you end the 
> transaction, which dumps the data from the cache, and then 
> you do it all again."
> 
> Does this mean that nothing in the cache is saved for other
> transactions?


Yes.  The data in the cache is only valid for one transaction.  The data 
in the cache is moved to the entity when an entity is loaded.  If you 
use Commit Option A (and I think B/D) your data will be saved across 
transactions.

 
> For many cases this is behavior is good, but often the same data is read
> again for other thansactions, for example different users logged in and
> are reading the same data. I thought this was some of the advantages of
> using CMP instead of plain JDBC-calls.


The interaction of read-only entities and the cache will not be 
addressed until the next release.

 
> What is the difference between "on-load" and "on-find"? Isn't on-load
> when the CMP is deployed?


neither of these have to do with deployment

on-load reads ahead (preloads) additional entities during the load. 
This is the repeated ORs you are seeing.  on-find reads additional data 
during the initial query.  This selects additional columns other then 
the required pk column in the query.


> Is this the same for read-only CMPs? Isn't the result saved?


read-only is currently independent of the read-ahead cache.  If a bean 
is read-only and it has data in the entity (as opposed to the cache) 
that data may be reused across the transaction.

> Another idee I have is that jbossCMP could tune itself, by logging
> access to CMPs, and select the best read-ahead, and field-loading
> . Or maybe a special mode (that might be slower), but give advice of
> what settings to use in jbosscmp-jdbc after you have runned some common
> use cases.

That would be sweet.  Do YOU know how to write an Expert system?  If you 
want to write it I would more than happy to help.

-dain