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
