Re: Adding batch update support in SimpleORM
anthony berglas <[email protected]> Sat, 2 Feb 2013 10:42:09 +1000
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <CA+_PZMfk4Q=a67Jv47chmMbZ5R4sFc4iOh2oDb__DqwzM94H8A@mail.gmail.com> |
--e89a8f83ab5fa832c404d4b323b1 Content-Type: text/plain; charset=ISO-8859-1 Excellent work. (The batching is all wrong in JDBC/Dbs. It should be just send any unrelated statements to the db, have them execute, and return a list of results for each. I have recently been working on KMIP, which almost gets that aspect right.) What sort of performane improvements did you get for Oracle and Postgresql? Anthony On Sat, Feb 2, 2013 at 2:22 AM, Franck Routier <[email protected]>wrote: > Hi all, > > I just commited a modification that add jdbc batch support in SimpleROM. > > By default, everything works as before, so this should have no impact on > running projects. > > But SSessionJdbc now has a method setUseBatchUpdate(boolean useBatch) that > will change its behaviour: when set to true, flushing will try to use batch. > > For all subsenquent dirty records in the dataset that result in the same > sql statement, it will create one only batch with different parameters, and > execute that batch in one jdbc call. > If the statement changes, it will execute the current batch and create a > new one. > This will be particularly useful if you want to insert or update a bunch > of records of the same SRecordMeta, on the same columns. Then the whole > dataset will be treated in one batch. > The main gain is that the network overhead is minimized when sending the > jdbc command. > > Benchmark shows significant performance improvements, even locally, with > Hsql (default ant target), Postgresql and Oracle. I could not test other > databases. > > Oh, great, so why not make it the default ? > > Well, this has a cost: as all (similar) records are sent in one batch, it > is much more difficult to find the faulty record in case of a SQLException. > For example, if one of your records breaks a constraint in the database, > you will get a SExecption.Jdbc error, but the getInstance() method will > return null, and you won't have acces to the faulty record... > > The other tradeoff, is that some SGenerator don't play well with this > approach. > Specifically, those that try to get the generated key after the record is > inserted, like SGeneratorInsertIdentity. > Then, if useBatchUpdate is set to true, it will gracefully fallback to the > traditional flush(ri)... Notice that Hibernate behaves the same with > IDENTITY. > If someone has an idea of a solution to that problem, I still created a > void postUpdateWithGeneratedKey(**SSessionJdbc session, SRecordInstance[] > dirtyRecords, int[] result). > This method is called after the batch is executed, and has an array of all > records in the batch... Then, I don't know what to do to get the ids... (I > don't use IDENTITY at all on my projects). > > Finally, I didn't test some databases I don't have access to: MS SqlServer > for example. There might be some work to do on the method SDriverXXX.**checkBrokenOptimisticLock, > if the driver does not return 1 or Statement.SUCCESS_NO_INFO (-2) on > success (MS SqlServer might return more than one, including records > modified by triggers, etc...). That said, batchUpdate is off by default, so > it does not break anything. > > I added a benchmark test. I also ran all the tests with batchUpdate=true > as default, and nothing broke, so I am pretty confident this works, but any > feedback is welcome ! > > Best regards, > > Franck > > -- Dr Anthony Berglas, [email protected] Mobile: +61 4 4838 8874 Just because it is possible to push twigs along the ground with ones nose does not necessarily mean that that is the best way to collect firewood. --e89a8f83ab5fa832c404d4b323b1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable <head> <style type=3D"text/css"> <!-- /* start of attachment style */ .ygrp-photo-title{ clear: both; font-size: smaller; height: 15px; overflow: hidden; text-align: center; width: 75px; } div.ygrp-photo{ background-position: center; background-repeat: no-repeat; background-color: white; border: 1px solid black; height: 62px; width: 62px; } div.photo-title=20 a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited { text-decoration: none;=20 } div.attach-table div.attach-row { clear: both; } div.attach-table div.attach-row div { float: left; /* margin: 2px;*/ } p { clear: both; padding: 15px 0 3px 0; overflow: hidden; } div.ygrp-file { width: 30px; valign: middle; } div.attach-table div.attach-row div div a { text-decoration: none; } div.attach-table div.attach-row div div span { font-weight: normal; } div.ygrp-file-title { font-weight: bold; } /* end of attachment style */ --> </style> </head> <html> <head> <style type=3D"text/css"> <!-- #ygrp-mkp { border: 1px solid #d8d8d8; font-family: Arial; margin: 10px 0; padding: 0 10px; } #ygrp-mkp hr { border: 1px solid #d8d8d8; } #ygrp-mkp #hd { color: #628c2a; font-size: 85%; font-weight: 700; line-height: 122%; margin: 10px 0; } #ygrp-mkp #ads { margin-bottom: 10px; } #ygrp-mkp .ad { padding: 0 0; } #ygrp-mkp .ad p { margin: 0; } #ygrp-mkp .ad a { color: #0000ff; text-decoration: none; } --> </style> </head> <body> Excellent work. =A0<div><br></div><div>(The batching is all wrong in JDBC/D= bs. =A0It should be just send any unrelated statements to the db, have them= execute, and return a list of results for each. =A0I have recently been wo= rking on KMIP, which almost gets that aspect right.)</div> <div><br></div><div>What sort of performane improvements did you get for Or= acle and Postgresql?</div><div><br></div><div>Anthony<br><div><br><div clas= s=3D"gmail_quote">On Sat, Feb 2, 2013 at 2:22 AM, Franck Routier <span dir= =3D"ltr"><<a href=3D"mailto:[email protected]" target=3D"_blank">= [email protected]</a>></span> wrote:<br> <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p= x #ccc solid;padding-left:1ex">Hi all,<br> <br> I just commited a modification that add jdbc batch support in SimpleROM.<br= > <br> By default, everything works as before, so this should have no impact on ru= nning projects.<br> <br> But SSessionJdbc now has a method setUseBatchUpdate(boolean useBatch) that = will change its behaviour: when set to true, flushing will try to use batch= .<br> <br> For all subsenquent dirty records in the dataset that result in the same sq= l statement, it will create one only batch with different parameters, and e= xecute that batch in one jdbc call.<br> If the statement changes, it will execute the current batch and create a ne= w one.<br> This will be particularly useful if you want to insert or update a bunch of= records of the same SRecordMeta, on the same columns. Then the whole datas= et will be treated in one batch.<br> The main gain is that the network overhead is minimized when sending the jd= bc command.<br> <br> Benchmark shows significant performance improvements, even locally, with Hs= ql (default ant target), Postgresql and Oracle. I could not test other data= bases.<br> <br> Oh, great, so why not make it the default ?<br> <br> Well, this has a cost: as all (similar) records are sent in one batch, it i= s much more difficult to find the faulty record in case of a SQLException.<= br> For example, if one of your records breaks a constraint in the database, yo= u will get a SExecption.Jdbc error, but the getInstance() method will retur= n null, and you won't have acces to the faulty record...<br> <br> The other tradeoff, is that some SGenerator don't play well with this a= pproach.<br> Specifically, those that try to get the generated key after the record is i= nserted, like SGeneratorInsertIdentity.<br> Then, if useBatchUpdate is set to true, it will gracefully fallback to the = traditional flush(ri)... Notice that Hibernate behaves the same with IDENTI= TY.<br> If someone has an idea of a solution to that problem, I still created a voi= d postUpdateWithGeneratedKey(<u></u>SSessionJdbc session, SRecordInstance[]= dirtyRecords, int[] result).<br> This method is called after the batch is executed, and has an array of all = records in the batch... Then, I don't know what to do to get the ids...= (I don't use IDENTITY at all on my projects).<br> <br> Finally, I didn't test some databases I don't have access to: MS Sq= lServer for example. There might be some work to do on the method SDriverXX= X.<u></u>checkBrokenOptimisticLock, if the driver does not return 1 or Stat= ement.SUCCESS_NO_INFO (-2) on success (MS SqlServer might return more than = one, including records modified by triggers, etc...). That said, batchUpdat= e is off by default, so it does not break anything.<br> <br> I added a benchmark test. I also ran all the tests with batchUpdate=3Dtrue = as default, and nothing broke, so I am pretty confident this works, but any= feedback is welcome !<br> <br> Best regards,<br> <br> Franck<br> <br> </blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><p> </p><p>Dr Anthony Berglas, <a href=3D"mailto:[email protected]" target=3D= "_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0 Mobile: +61 4 4838 8874<= br> Just because it is possible to push twigs along the ground with ones nose<b= r> does not necessarily mean that that is the best way to collect firewood.</p= > </div></div> <!-- |**|begin egp html banner|**| --> <br> =20=20=20=20 =20=20=20=20 <br> <!-- |**|end egp html banner|**| --> <div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div> <!-- Start Recommendations --> <!-- End Recommendations --> <!-- |**|begin egp html banner|**| --> <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D7360226/grpspI= d=3D1705006905/msgId=3D2072/stime=3D1359765771" width=3D"1" height=3D"1"> <= br> <!-- |**|end egp html banner|**| --> =20=20 <!-- |**|begin egp html banner|**| --> <br> <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s= olid #666; padding: 5px 0;" > Your email settings: Individual Email|Traditional <br> <a href=3D"http://groups.yahoo.com/group/SimpleORM/join;_ylc=3DX3oDMT= JmbGduOXA2BF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzNTk3NjU3NzE-">Change settings via the Web</a= > (Yahoo! ID required) <br> Change settings via email: <a href=3D"mailto:SimpleORM-digest@yahoogr= oups.com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest<= /a> | <a href =3D "mailto:[email protected]?subject=3D= Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br> <a href=3D"http://groups.yahoo.com/group/SimpleORM;_ylc=3DX3oDMT= JkdDliZ3Y2BF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA2hwZgRzdGltZQMxMzU5NzY1Nzcx"> Visit Your Group=20 </a> | <a href=3D"http://docs.yahoo.com/info/terms/"> Yahoo! Groups Terms of Use </a> | <a href=3D"mailto:[email protected]?subject=3DUns= ubscribe"> Unsubscribe=20 </a>=20 <br> </div> <br> <!-- |**|end egp html banner|**| --> <div style=3D"color: white; clear: both;"/>__,_._,___</div> </body> </html> --e89a8f83ab5fa832c404d4b323b1--