db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Philippe Guillard <pguill...@citycita.net>
Subject Re: JDO Query / indexed column
Date Fri, 03 Jun 2005 08:29:24 GMT
Hi all,

Really strange, this summurizes exactly my problem:

Using JDOQuery, my database index is not used query needs 6/7/8s to 
commit on big table,
logs from MySql says:
SELECT A0.ZIP,..... FROM USAZIP A0

BUT using OJB Query, i get instantly the result.
logs from MySql says
SELECT A0.ZIP,..... FROM USAZIP A0 WHERE A0.ZIP = '90001'
(I didn't try P6Spy yet).

Do you think it is an acceptable practice to use a mix of JDO (all my 
retrieve, update, remove functions are with JDO pm.getObjectById() )
 and add some OJB queries using The PersistenceBroker API for somes 
queries with more complicated criteria on indexed fields?

Regards,

Phil

____________________________________________________________________
    // JDO query 8s
    public static Collection queryTest1(PersistenceManager pm) {
        Query query = pm.newQuery(Usazip.class, "zip==\"90001\"");
        Collection collection = (Collection) query.execute();
        return collection;
    }
   
    //With parameters 7s
    public static Collection queryTest2(PersistenceManager pm) {
        Query query = pm.newQuery(Usazip.class, "zip==param");
        query.declareParameters("String param");
        Collection collection = (Collection) query.execute("90001");
        return collection;
    }
   
    //query all 5/6s is less than with criteria on indexed column!!!
    public static Collection queryTest3(PersistenceManager pm) {   
        Query query = pm.newQuery(Usazip.class);
        Collection collection = (Collection) query.execute();
        return collection;
    }


    //Directly OJB broker  <1s
    public static Collection brokerTest2() {
        PersistenceBroker broker = null;
        Collection collection = null;
        try
        {
            broker = PersistenceBrokerFactory.defaultPersistenceBroker();
            Criteria crit = new Criteria();
            crit.addEqualTo("zip","90001");
            QueryByCriteria query = new QueryByCriteria(Usazip.class, crit);
            collection = broker.getCollectionByQuery(query);
            return collection;
        }
        finally
        {
            if (broker != null) broker.close();
        }
    }






Philippe Guillard wrote:

> Thanks a lot Martin, i'll give a try..
> Phil
>
> Martin Kalén wrote:
>
>> Philippe Guillard wrote:
>>
>>> Thanks Charles,
>>>
>>> Unfortunately i get the same result without parameter. (  I use JDO 
>>> 1.01 form Sun, OJB as integrated in cocoon framework).
>>> You guess right i 've built the DB by hand before and adapted a 
>>> repository later. It is not supposed to be used at runtime, but i 
>>> get errors at runtime in case of incorrect configuration, thus i 
>>> tried configuring indexes in repository...
>>> Any idea for which direction i should go?
>>
>>
>>
>> The index-descriptor is (as Charles pointed out) only used if you 
>> generate
>> DDL/SQL with OJB using Torque.
>>
>> If you create your database "by hand", you can remove all 
>> index-descriptor
>> attributes to get a cleaner OJB repository.
>>
>> Also, the following is a key issue:
>>
>>> Charles Anthony wrote:
>>>
>>>> The database always decides what indexes to use, never OJB !
>>>
>>>
>>
>> What you should do to get OJB to use database indexes is use P6Spy or
>> the logtrace from your RDBMS, inspect the generated statements and
>> then set indices accordingly (so that OJB-generated SQL can use
>> indices).
>>
>> There are some 3rd party tools to help you time Statement execution
>> times and give you hints for what to set your index on (eg [1], [2]).
>>
>> Regards,
>>  Martin
>>
>> [1] IronGrid IronTrack - built on top of bundled P6Spy
>>     Presents graphical trace, "culprits" and timeline of Statement times
>>     http://www.irongrid.com/catalog/product_info.php?products_id=32
>>
>> [2] Jahia SQL Profiler - integrates with P6Spy
>>     Somewhat old and simpler than IronTrack, but can autogenerate 
>> index DDL
>>     http://www.jahia.org/jahia/page377.html
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org


Mime
View raw message