You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Martin Kalén <mk...@apache.org> on 2005/06/01 02:06:44 UTC
Re: JDO Query / indexed column
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
Re: JDO Query / indexed column
Posted by Philippe Guillard <pg...@citycita.net>.
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
Re: JDO Query / indexed column
Posted by Philippe Guillard <pg...@citycita.net>.
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
Re: JDO Query / indexed column
Posted by Martin Kalén <mk...@apache.org>.
Martin Kalén wrote:
> 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.
Clarification: The index-descriptor attribute is only used to get
"CREATE INDEX" DDL in the Torque-generated script.
Even if you specify index-descriptor attributes OJB will still not
use the indexes automagically when performing queries against the DB,
since query generation is not (cannot be) changed due to any
index-descriptors.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org