Hi, The best course of action is to find a way to split it into multiple transactions and not wait for the fix. Priority 1 isn't a guarantee the feature will be added soon -- server-side cursors have been priority 1 since last August. This problem is likely to be fixed sooner, but because the problem is architectural it may require significant time to fix. Or it might be fixed overnight; Thomas is one of the fastest coders I've seen, but is quite busy, so all bets are off.
The old storage engine runs very slowly with large updates and numerous-row updates (as I believe you discovered, if you were using old storage engine). The new page store is much faster for large transactions, but they can cause out of memory errors, and the code still isn't as robust for this use case. With the new page store, I wouldn't try updating more than a couple hundred thousand rows at a time. That is, unless you give the DB a LOT of extra heap without increasing cache size. Take your pick. I'll try to add some new test cases covering large transactions and queries (assuming Thomas hasn't already). These sorts of tests take a long time to run, so it may be a while before they are used. Cheers, Sam Van Oort (Junior Programmer, H2 project) On Jan 22, 3:48 am, Ryan How <[email protected]> wrote: > Hi, > > I had a need to import a bulk lot of data that will both insert new data > and update existing data in several tables. I have this in a transaction > because I don't want any of it to go ahead if any errors occur. It has > been working great, but I recently tried importing quite a lot of data > and after the H2 cache filled up the inserts started going very slow. I > increased the cache size and the jvm max heap to 512MB and it went > blazingly quick. > > I now have a larger lot of data to import and I don't want to increase > the cache size any higher. I noticed priority 1 in the roadmap is Issue > 157: Support large inserts and updates (use the transaction log for > rollback). Is this what I need to wait for or are there some other > settings I can tweak? > > The other alternative is to change the import process so I don't need to > do it all in one large transaction, but it would be easier if H2 can > handle it :). > > Cheers, > > Ryan -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
