On Oct 18, 2010, at 10:34 PM, Borut Bolčina wrote:
> Hi,
>
> what makes insert faster when doing it every 100 objects? In each case the
> same number of inserts will be generated.
batching stuff together is usually faster (this relates to JDBC batching described below, or more generally to batching of multiple Cayenne operations in a single transaction). Can't put an exact number on it, but there's definitely some Cayenne, JDBC and DB overhead processing each commit which is not related to the number of objects committed.
> Can, beside using templates, Cayenne be configured to do something like:
>
> INSERT INTO *tbl_name* (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
>
>
> That is one insert for 3 rows, which is more efficient (mysql).
Cayenne supports standard JDBC batching (i.e. generating queries with multiple sets of parameters like the above, using JDBC batching API). This is controlled by DbAdapter.supportsBatchUpdates(). Oracle, PostgreSQL, SQLServer adapters all return true, MySQL adapter returns false. Back in the day batch updates weren't an option on MySQL. Not sure what's the status of that now. Even if MySQL supports that, the driver may not. We may need to research it.
In the meantime you can experiment forcing this flag on MySQLAdapter (or use a custom adapter subclass to do that), and give us feedback. I'll be glad to change the default on MySQL if it actually works with modern drivers.
Andrus
This archive was generated by hypermail 2.0.0 : Mon Oct 18 2010 - 19:58:46 UTC