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 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]
