Multiple inserts to oracle in one roundtrip RRS feed

  • Question

  • Hi,

    In my form I have so many textboxes and check boxes and Gridview, When the user clicks on a button I need to insert those data into different tables in Oracle database.

    Can anyone tell me the best way to do the multiple inserts in to different tables in Oracle database.

    Thanks in advance

    Monday, July 23, 2007 8:16 PM

All replies

  • I investigated doing something like this for Sql Server 2000 a while back; you may be able to use a similar solution for Oracle.

    Basically we had a lot of data to send to the Sql Server, including inserts, updates and deletes for many different tables. We wanted to do it all in a single round trip.

    We were using typed datasets to store our data, so I produced a diffgram from the typed dataset. I submitted this Xml to my stored procedure, which parsed it using the OpenXml command. It then used XPath queries to extract what it needed to update, insert and delete and into which tables. Of course you could produce the Xml by other means.

    This all worked swimmingly, but it's certainly not trivial. For small amounts of data it'll be slower than the traditional multiple-round-trips method, but the efficiency increases for larger amounts. I don't know at what size of data the bulk method becomes more efficient than the one-at-a-time method though, or even if it ever does.

    The reason I went to so much trouble was that we were coming from using a Sybase server with VB5, through ODBC, and the cost of each roundtrip was very significant. Changing to bulk processing gave massive performance benefits, 100X and more. However we found that using Sql Server with ADO.NET the cost of each roundtrip was far smaller, and therefore the benefit of bulk processing wasn't necessarily worth the extra development required.

    I left that company before the final determination was made, so I don't know which way they went. I believe I still have the documentation I wrote if you'd find that useful; it gives full details of how to do what I've described, including examples of the XPath queries etc.

    To be honest I'd only go this way if you really need to though, generally more traditional methods offer enough performance.


    Monday, July 30, 2007 6:33 PM
  • As it happens someone else posted elsewhere about a different technique that I'd seen used but forgotten about.

    You can generate a load of insert statements client-side and send the resulting Sql script as a string to your SP. I don't know how you execute dynamic Sql in Oracle but it must be possible.

    It's a bit primitive but should be quick.

    Wednesday, August 8, 2007 9:08 PM