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 <[email protected]> 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 <[email protected]> > >> 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 >> >
