Re: Updating Relational Data with XML.

Daniel, first of all thanks for taking the time to answer my question. We are developing a new software application in house. This software generates revenue schedules and for some of our larger clients this could be up to 100,000 records per operation and this could happen several times a day. Now, our software is built completely around .NET and SOA and the database server (we support sql and oracle on the back end) will most likely not live on the same box as our application. Our goal is to avoid having to make N database calls for N revenue schedules and we have done a POC that clearly shows that when we have this many records going into the database (inserts only for multiple tables) the overhead of opening and re-opening the connection to call a procedure or package (in the case of oracle back end) is large. This coupled with network latency issues could make the process fall outside the accepted defined performance requirements. To pass once large clob or text object to a package or procedure would remove all that latency
(network and connnection) and the overhead of parsing the xml is far
less and would save much time. For example our app did a simple insert
(three tables involved: Rev Header, Rev Detail, Account Detail) of
20,000 schedules and it took about 12 minutes to complete. I had done a trace on the database server and found the average time on the database server per schedule operation was only 3 ms which only accounted for only 1 minute of the 12 minutes of total processing time from the application end.

Since I posted yesterday I figured out how to do the inserts with Oracle (I already understand how to do it with MSSQL) using xpath if the insert involves multiple tables.

If you have first hand knowledge of this type of problem I would appreciate any words of wisdom that you may impart.

TFD Received on Wed Apr 27 2005 - 09:07:33 CDT