Hi,
The reasoning behind batching all the inserts is performance. From the
few tests I ran locally, I managed to decrease the time it took to
write 1000 inserts by about 4-5 times by batching them all into one DB
roundtrip (with a BatchExecutor). Even if the latency for a single DB
roundtrip is 1ms, for 1000 roundtrips like that you get 1s latency.
I would like to relax the requirements I mentioned earlier - The
INSERT and DELETE statements do not need to be aware of each other, if
a record is inserted into the DB and deleted a few ms later, so be it.
I actually came up with a solution to this issue, though I'm not sure
if its the most efficient one:
What I want to do is keep a buffer of all INSERT and DELETE statements
locally, and have a 'consumer' thread that flushes them in batches to
the DB. The thread would sleep for intervals of 1 second, and then
flush all the INSERT statements and then all the DELETE statements.
All the INSERT and DELETE statements differ one from another only in
the parameters they receive, so they can be batched in a single DB
roundtrip (via BatchExecutor).
Is this the best way to achieve this with MyBatis?
Also, are there any special pointers that need to be taken to
integrate this with Spring?
Thanks!
Yevgeny.
On Feb 29, 4:32 am, Jeff Butler <
[hidden email]> wrote:
> MyBatis won't do this.
>
> My first question is...why? This is what a database is for. What is
> the benefit you are hoping to gain? In my experience, if you have a
> good connection pool then talking direct to the database is pretty
> quick.
>
> If you feel you have to do this, then what about using something like
> HSQL in memory locally to create the batch, then do a batch insert to
> the remote db? You might also be able to do the pre-processing with a
> HashMap depending on the complexity of the key structure.
>
> Jeff Butler
>
>
>
>
>
>
>
> On Tue, Feb 28, 2012 at 5:21 AM, Yevgeny Krasik <
[hidden email]> wrote:
> > Hi,
>
> > I have a few questions regarding if/how the following can be done in
> > MyBatis:
>
> > 1) I'm trying to batch a mix of INSERT/DELETE statements into a single
> > roundtrip to the DB. For example, I would like to accumulate a mix of
> > 2000 (possibly inter-dependent) statements into a batch, and then
> > flush them all to the DB in one go. Each statement needs to be aware
> > of previous statements, so a DELETE statement will be able to delete a
> > record that was previously INSERTed in the same batch, but possibly
> > not yet flushed to the DB, avoiding writing it to the DB altogether.
> > Can this be done with MyBatis?
>
> > 2) Supposing the above is possible, I would now like to add a timeout
> > to the batch flush, so the batch would be flushed once it has
> > accumulated 2000 queries OR 1 second has passed.
>
> > 3) And finally, are there any special measures that need to be taken
> > to make all of this work with Spring?
>
> > Your help would be greatly appreciated!
> > Thank you,
> > Yevgeny.
Locked
