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: [email protected]
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]
