> 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.
Thanks for the hint. How can I make sure that batching is used (I am using
Oracle)?
Is it used normally when I am manipulating data objects (as it seems so from
the logs...)?
And how does PK caching affect performance?
Sorry for my basic questions, I am a newbie in the DB world...
> > 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.
Yes, you are right of course. I was considering only the current performance
test where I update or delete a large number of objects based on a simple
select.
Cheers,
Péter
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
>
