[OJB-developers] Re: OQLQueryImpl effieciency

[Thomas M. <tho...@ho...>]

Hi again Charles,


Charles Anthony wrote:
> 
> Hi all,
> 
> Whilst using OJB in a prototype, I found that using the standard
> ojb.odmg.oql.OQLQueryImpl class to do an OQL query seemed a little
> inefficient.
> 
> It turns out that this is because OQLQueryImpl uses a DList to return the
> results as a collection. The DList implementation access the OJB_SEQ table
> to gain an ID for itself, and one for each entry in the list.
> 

You are right. Using DList for certain circumstances may be inefficient.
Using a DList for a collection result is also not specified by ODMG.
I choose to use DList for the following reasons:
1. it supports a lot of useful interfaces: Collection, List,
DCollection, DList.
2. it is persistence capable. That is you can store the result of a
query for subsequent operations.
3. it integrates into the ODMG transaction mechanism.

Of course it is not always necessary to have all these features.
In certain scenarios, where I did not need any of the above features, I
used the simple getCollectionByQuery(query) method that just returns a
java.util.Vector to improve performance...

> I have attached a quick-n-dirty try to improve on this; basically, I've
> implemented a ManageableArrayList collection, which extends ArrayList and
> implements ManageableCollection. I've also implemented a SpeedyQuery class,
> which extends OQLQueryImpl and overrides execute to return a
> ManageableArrayList instead of a DList. I've also attached a simple
> CompareQueries class that tests the SpeedyQuery class.
> 
> In this test (against a table with 1889 rows on SQL server), the
> OQLQueryImpl took on average 37 secs. The SpeedyQuery took 1.5 secs.
> 

A side note: could you send me the SQL Server  settings for sql1.txt and
the JDBCConnectionDescriptor?
Did you have any problems with OJB and SQL server? Was it necessary to
make changes to SQL1.txt or the repository.xml apart from JDBC
connections?

> I'm sure there are drawbacks to this approach, and I'm sure that I've
> overlooked something terribly important. The only things that I note at the
> moment are :
> 
> * I'm not currently registering the resulting items with the current
> transaction; this would be
>   trivial to achieve.

This is important to provide transactional integrity. each object
returned by a query should be read-locked immediately.

> * If you remove or add an item to the ManageableArraylist, the changes would
> not be propogated to
>   the database.

If we look at a result collection as a snapshot, updates are not
necessary...
> 

One might add: thread safety.

> Still, even with those caveats (which I'm sure are possible to deal with), I
> still think this speed improvement is not to be sniffed at.
> 

Right: 1.5 versus 37 is quite a difference. 

> Comments are welcome; just be gentle with me - this is the first code I have
> sent to an open-source mailing list....
>

Thanks for you contribution! I will try to integrate it into the main
distribution if you don't mind.
My idea is to have a special configuration entry in OJB.properties that
determines which Collection type is to be used for OQL queries.

Maybe it's also a good idea to have the classes that are use to
implement certain ODMG interfaces configurable. This way it will be a
lot easier to allow experimentation with user defined stuff like your
SpeedyQuery Class...


Thanks a lot again for you contribution,

--Thomas