Hi Borut,
If you did 100 at a time, Cayenne would create 1 insert statement with
100 binds instead of your current situation of 100 inserts with 100
binds (if you were only doing 100). Admittedly, the insert could
potentially be compiled/cached somewhere (as a PreparedStatement), but
you still have to deal with the database setting up 100 transactions
and your network I/O is substantially higher one-at-a-time, too.
Plus, there is a cost involved with Cayenne examining the objects in
the DataContext for changes (and the objects are growing in your
DataContext) each commit. So you are scanning for changes much more
often with one-at-a-time. I'd even suggest throwing away your
DataContext after you commit the 100 objects and create a new
DataContext.
The template thing you mentioned is more-or-less what Cayenne is doing
with 1 insert statement and 100 binds.
mrg
PS. The number 100 is arbitrary. Your sweet spot may be 10, 50, 200,
500, 1000, etc. Play around with a few values to find what works well
for you time/memory-wise given your scenario. Values of 1 and 500k
are going to be less optimal, though. :-)
On Mon, Oct 18, 2010 at 3:34 PM, Borut Bolčina <borut.bolcin..mail.com> wrote:
> Hi,
>
> what makes insert faster when doing it every 100 objects? In each case the
> same number of inserts will be generated.
>
> 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).
>
> -Borut
>
> 2010/10/18 Michael Gentry <mgentr..asslight.net>
>
>> Hi Borut,
>>
>> In addition to what Andrus said about caching Towns, Streets, and
>> PostOffices, you should also try to do your commits in larger
>> groupings. One commit at a time will be slower than you want for
>> something like this. Try grouping 100 or so at a time before you
>> commit.
>>
>> mrg
>>
>
This archive was generated by hypermail 2.0.0 : Mon Oct 18 2010 - 19:50:06 UTC