Re: JDO Query / indexed column
Philippe Guillard wrote: Then i red somthing i should read before in FAQ section 3.3 I recommend to not use JDO now, but to use the existing ODMG api for the time being and wonder if this would affect my problem too... Any suggestion / what i should do next? If you have any doubts about the status/completeness of the different APIs included in Apache OJB, you can always check the following page: http://db.apache.org/ojb/status.html If you want to use something production-ready, the choice between ODMG and JDO is easy: ODMG. If you explicitly want to use the JDO standard and don't mind the current plug-in soloution and beta status in OJB you can still stick with OJB JDO. If you are searching for a production-ready JDO 1 implementation right now, you are better off with JPOX: http://www.jpox.org/ Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
Thanks, Excuse for my ignorance, 2 last questions: - In the JPOX case it means using JPOX instead of OJB, right? - Apache licensed JDO 2.0 implementation is scheduled for OJB 2.0. Is there info somewhere about the schedule? (In that case i would wait, i'm not in production yet) Regards, Phil Martin Kalén wrote: Philippe Guillard wrote: Then i red somthing i should read before in FAQ section 3.3 I recommend to not use JDO now, but to use the existing ODMG api for the time being and wonder if this would affect my problem too... Any suggestion / what i should do next? If you have any doubts about the status/completeness of the different APIs included in Apache OJB, you can always check the following page: http://db.apache.org/ojb/status.html If you want to use something production-ready, the choice between ODMG and JDO is easy: ODMG. If you explicitly want to use the JDO standard and don't mind the current plug-in soloution and beta status in OJB you can still stick with OJB JDO. If you are searching for a production-ready JDO 1 implementation right now, you are better off with JPOX: http://www.jpox.org/ Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
Philippe Guillard wrote: Excuse for my ignorance, 2 last questions: - In the JPOX case it means using JPOX instead of OJB, right? Yes, that's right - JPOX != OJB product and JPOX currently have a free-standing JDO implementation (as opposed to OJB's RI plug-in). - Apache licensed JDO 2.0 implementation is scheduled for OJB 2.0. Is there info somewhere about the schedule? (In that case i would wait, i'm not in production yet) Well, I don't think there is a timed schedule but a resonable time horizon to expect is: * in the near future v1.0.4 maintenance release (still JDO RI plug-in) * in the near future v1.1 technology preview (still JDO RI plug-in) * in a few months time v1.1 release (still JDO RI plug-in) * in a long time OJB v2.0 release with complete JDO 2.0 API implementation Current focus is on getting v1.1 out the door, so priorities might shift once this happens. (Eg after a successful v1.1 release without too much follow-up bugfix work it might happen that JDO development becomes more active and that an interim v1.5 OJB release has more complete JDO support.) As always with open source development, the time schedule is very much dependent on the OJB JDO developers' available amount of time to spend on OJB development. All help is appreciated and can speed things up! Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
Thanks Martin. Martin Kalén wrote: Philippe Guillard wrote: Excuse for my ignorance, 2 last questions: - In the JPOX case it means using JPOX instead of OJB, right? Yes, that's right - JPOX != OJB product and JPOX currently have a free-standing JDO implementation (as opposed to OJB's RI plug-in). - Apache licensed JDO 2.0 implementation is scheduled for OJB 2.0. Is there info somewhere about the schedule? (In that case i would wait, i'm not in production yet) Well, I don't think there is a timed schedule but a resonable time horizon to expect is: * in the near future v1.0.4 maintenance release (still JDO RI plug-in) * in the near future v1.1 technology preview (still JDO RI plug-in) * in a few months time v1.1 release (still JDO RI plug-in) * in a long time OJB v2.0 release with complete JDO 2.0 API implementation Current focus is on getting v1.1 out the door, so priorities might shift once this happens. (Eg after a successful v1.1 release without too much follow-up bugfix work it might happen that JDO development becomes more active and that an interim v1.5 OJB release has more complete JDO support.) As always with open source development, the time schedule is very much dependent on the OJB JDO developers' available amount of time to spend on OJB development. All help is appreciated and can speed things up! Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
Thanks Charles,
Right, i was still confusing, i'm in the fundamental problem case, no
where cititeria, and don't know what to do next.
Between i didn't succed to get p6spy logs, and discoverd following the
OjectCache instructions : caching works for me with PB and not with JDO!
Then i red somthing i should read before in FAQ section 3.3 I recommend
to not use JDO now, but to use the existing ODMG api for the time being
and wonder if this would affect my problem too...
Any suggestion / what i should do next?
Regards,
Phil
Charles Anthony wrote:
Hi,
I'm sorry to keep harping on about it, but your problem really is not the
index or indexed fields - indexs really don't come into it, at least not
yet.
Your fundamental problem is that the JDO query is not generating a where
criteria in the SQL, whereas the OJB Query is. And that is a very
fundamental problem...
As I said before, I know nothing about JDO - but in our use of OJB, we are
combining both the ODMG api and OJB PB Queries, and everything works fine.
So, yes, I don't see any technical problem with combining the two.
Cheers,
Charles
-Original Message-
From: Philippe Guillard [mailto:[EMAIL PROTECTED]
Sent: 03 June 2005 09:29
To: OJB Users List
Subject: Re: JDO Query / indexed column
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
Re: JDO Query / indexed column
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: JDO Query / indexed column
Hi,
I'm sorry to keep harping on about it, but your problem really is not the
index or indexed fields - indexs really don't come into it, at least not
yet.
Your fundamental problem is that the JDO query is not generating a where
criteria in the SQL, whereas the OJB Query is. And that is a very
fundamental problem...
As I said before, I know nothing about JDO - but in our use of OJB, we are
combining both the ODMG api and OJB PB Queries, and everything works fine.
So, yes, I don't see any technical problem with combining the two.
Cheers,
Charles
-Original Message-
From: Philippe Guillard [mailto:[EMAIL PROTECTED]
Sent: 03 June 2005 09:29
To: OJB Users List
Subject: Re: JDO Query / indexed column
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL
Re: JDO Query / indexed column
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: JDO Query / indexed column
Hi Phillipe,
The database always decides what indexes to use, never OJB ! OJB knows
nothing about indexes (the repository.xml entry is, or used to be, used to
generate the SQL/DDL to create the tables in the database. It isn't used at
runtime)
The SQL query that has been generated *doesn't* use the ZIP field in the
where clause of the query, and hence the index is not used.
I have never used the JDO API with OJB (or indeed any JDO implementation),
but I would guess that the use of a parameterised query is stretching the
capabilities of the combination of the sun reference implementation and the
OJB plugin.
Try something like this (rememver, this mighr be complete rubbish as I've
never used JDO):
Query query = persistenceManager.newQuery(Usazip.class, zip=='10001');
Collection collection = (Collection) query.execute();
I hope and imagine this will generate
SELECT A0.IDZIP,A0.ZIP FROM ZIP A0 WHERE A0.ZIP = '10001'
That would, in turn, use the database's index on the ZIP column.
HTH a little,
Charles.
-Original Message-
From: Philippe Guillard [mailto:[EMAIL PROTECTED]
Sent: 28 May 2005 13:57
To: ojb-user@db.apache.org
Subject: JDO Query / indexed column
Hi all,
I use OJB/ JDO API. I know i should forget here thinking the
relational
way, but i wonder if my DB indexes are used when i execute a
JDOQL query
on a field that is not a primary Key but just indexed field.
(I ask this
because in case of Primary Key i suppose people here would
suggest using
getObjectById())
Adding the index in my DB doesn't change anything so i
suppose it is not
used, and wonder about querying a large table?
(Forgot to say : newbie question:-))
Here is my quick sample: Zip codes table with (stupid but for
example)
idzip as PK meaning nothing, and the zip code as a String field on
which i added index in databse :
CREATE INDEX testIndex ON ZIP(zip);
JAVA CLASS
public class Zip implements Serializable {
private int idzip;
private String zip;
public void setIdzip(int newIdzip) {
this.idzip = newIdzip;
}
public void setZip(String newZip) {
this.zip = newZip;
}
}
REPOSITORY
class-descriptor class=net.talkgroups.model.bean.Usazip
table=USAZIP
field-descriptor name=idzip
column=idzip jdbc-type=VARCHAR primarykey=true/
field-descriptor name=zip column=zip
jdbc-type=VARCHAR indexed=true access=readonly/
!-- also tried this
index-descriptor name= testIndex unique= true
index-column name= zip/
/index-descriptor
--
/class-descriptor
JDO QUERY
Query query = persistenceManager.newQuery(Usazip.class, zip==param);
query.declareParameters(String param);
Collection collection = (Collection) query.execute(10001);
// 10001 is
zip code
I get these logs from DB:
050528 19:56:05 27 Query SELECT 1
27 Query SET autocommit=0
27 Query SELECT A0.IDZIP,A0.ZIP FROM ZIP A0
050528 19:56:15 27 Query commit
It is slow in all cases (with or without index), and much faster with
SQL in my DB client. (table is US zip codes)
Regards
Phil
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
___
HPD Software Ltd. - Helping Business Finance Business
Email terms and conditions: www.hpdsoftware.com/disclaimer
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: JDO Query / indexed column
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?
Regards,
Phil
Charles Anthony wrote:
Hi Phillipe,
The database always decides what indexes to use, never OJB ! OJB knows
nothing about indexes (the repository.xml entry is, or used to be, used to
generate the SQL/DDL to create the tables in the database. It isn't used at
runtime)
The SQL query that has been generated *doesn't* use the ZIP field in the
where clause of the query, and hence the index is not used.
I have never used the JDO API with OJB (or indeed any JDO implementation),
but I would guess that the use of a parameterised query is stretching the
capabilities of the combination of the sun reference implementation and the
OJB plugin.
Try something like this (rememver, this mighr be complete rubbish as I've
never used JDO):
Query query = persistenceManager.newQuery(Usazip.class, zip=='10001');
Collection collection = (Collection) query.execute();
I hope and imagine this will generate
SELECT A0.IDZIP,A0.ZIP FROM ZIP A0 WHERE A0.ZIP = '10001'
That would, in turn, use the database's index on the ZIP column.
HTH a little,
Charles.
-Original Message-
From: Philippe Guillard [mailto:[EMAIL PROTECTED]
Sent: 28 May 2005 13:57
To: ojb-user@db.apache.org
Subject: JDO Query / indexed column
Hi all,
I use OJB/ JDO API. I know i should forget here thinking the
relational
way, but i wonder if my DB indexes are used when i execute a
JDOQL query
on a field that is not a primary Key but just indexed field.
(I ask this
because in case of Primary Key i suppose people here would
suggest using
getObjectById())
Adding the index in my DB doesn't change anything so i
suppose it is not
used, and wonder about querying a large table?
(Forgot to say : newbie question:-))
Here is my quick sample: Zip codes table with (stupid but for
example)
idzip as PK meaning nothing, and the zip code as a String field on
which i added index in databse :
CREATE INDEX testIndex ON ZIP(zip);
JAVA CLASS
public class Zip implements Serializable {
private int idzip;
private String zip;
public void setIdzip(int newIdzip) {
this.idzip = newIdzip;
}
public void setZip(String newZip) {
this.zip = newZip;
}
}
REPOSITORY
class-descriptor class=net.talkgroups.model.bean.Usazip
table=USAZIP
field-descriptor name=idzip
column=idzip jdbc-type=VARCHAR primarykey=true/
field-descriptor name=zip column=zip
jdbc-type=VARCHAR indexed=true access=readonly/
!-- also tried this
index-descriptor name= testIndex unique= true
index-column name= zip/
/index-descriptor
--
/class-descriptor
JDO QUERY
Query query = persistenceManager.newQuery(Usazip.class, zip==param);
query.declareParameters(String param);
Collection collection = (Collection) query.execute(10001);
// 10001 is
zip code
I get these logs from DB:
050528 19:56:05 27 Query SELECT 1
27 Query SET autocommit=0
27 Query SELECT A0.IDZIP,A0.ZIP FROM ZIP A0
050528 19:56:15 27 Query commit
It is slow in all cases (with or without index), and much faster with
SQL in my DB client. (table is US zip codes)
Regards
Phil
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
___
HPD Software Ltd. - Helping Business Finance Business
Email terms and conditions: www.hpdsoftware.com/disclaimer
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
