> What I found was that in Insert operations, it is best to use
> Cayenne data objects (as opposed to raw SQL queries)
Cayenne adapters for Oracle, Derby, FrontBase, Postgres and SQLServer
use JDBC-level batching that sometimes speeds things up
significantly. SQLTemplate doesn't use JDBC batching. There are other
possible reasons too, such as pk caching.
> while for Update and Delete the case is the opposite.
Here you are not comparing apples to apples. A single update (or
delete) query that matches some condition will certainly be faster
than a series of update queries (or a batched update query for
multiple objects), but the two are different logical operations. I
guess you should choose which one to use depending on circumstances.
Andrus
On Mar 12, 2007, at 4:40 PM, Török Péter wrote:
> Sorry for the previous mail, I inadvertently sent it half-ready :-(
> Hello,
> I made a small performance test comparing different Cayenne
> solutions with JDBC solutions. What I found was that in Insert
> operations, it is best to use Cayenne data objects (as opposed to
> raw SQL queries), while for Update and Delete the case is the
> opposite. I guess this may not surprise you :-)
> So for Insert, this worked best for me:
>
> public void insertRecords(int count) {
> Date date = new Date();
> for (int index = 1; index <= count; index++) {
> TestData testData = (TestData) context.newObject
> (TestData.class);
> testData.setName(nextName());
> testData.setDescription(nextDescription());
> testData.setCount(new Integer(count - index));
> testData.setCreated(date);
> if (index % 1000 == 0) {
> context.commitChanges();
> }
> }
> context.commitChanges();
> }
>
> while for Update and Delete, something like this:
>
> public void updateRecords(int lowerLimit, int upperLimit) {
> final SQLTemplate query = new SQLTemplate(TestData.class,
> "update test_data set description = '$desc' where count >
> $lower and count < $upper");
> Map params = new HashMap();
> params.put("desc", nextDescription());
> params.put("lower", new Integer(lowerLimit));
> params.put("upper", new Integer(upperLimit));
> context.performNonSelectingQuery(query.createQuery(params));
> }
>
> Any hints on how to make these methods faster?
> Thanks in advance,
> Péter
>
This archive was generated by hypermail 2.0.0 : Mon Mar 12 2007 - 11:00:54 EDT