Updating multiple records

[Bala Nagarajan]

Hello,
I want to know an efficient of updating multiple records in a .NET
datatable into a Oracle 9i databse without making multiple trips to the
database. That is, i want to know if Oarcle 9i can take the whole set of
records in the datatable in a stored procedure(in the form of XML or arrays)
and update records internally by looping through the records. I want to
acheive this using the OarcleClient that come with .NET 1.1.

I am currently experiencing huge delays(30 minutes) when i try to update
around 15000 records through the DataAdapters.Update method (I think the
delay is due to the fact the stored procedure is invoked for every record).
The database is in our intranet and is properly indexed on the update table.


Any thoughts on this?

I apperciate your help!

Thanks
Bala

 

[Jeff Dillon]

DiffGrams?

 

[Bala Nagarajan]

Jeff,
Thanks for replying. Can you please elaborate on this?

Thanks
Bala

 

[Jeff Dillon]

Did you look? Google "diffgrams oracle"

Jeff,
Thanks for replying. Can you please elaborate on this?

Thanks
Bala

 

[Cowboy \(Gregory A. Beamer\)]

This is not a problem unique to .NET. The sheer number of records you are
attempting to update is problematic with any system not directly connected.



To your questions:

Can you batch with XML? Yes, but be careful doing a full batch of 15,000
records. Another option is create a bulk update load file and then bulk it
to a temp location and perform updates. Much faster. The downside is you
create a much heavier mechanism. If you want to go XML, you can also chunk
your batches.



NOTE: If this is a batch process, which it probably is with than many
records, ADO.NET built in FUD is NOT your best option.


--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Kevin Yu [MSFT]]

Hi Bala,

If you're updating the Oracle database using OracleDataAdapter, you can
also try to set the OracleDataAdapter.UpdateBatchSize property. It will
decrease the round trips to the database.

Please check the following link for more information.

http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracledata
adapter.updatebatchsize.aspx

HTH.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Bala Nagarajan]

Kevin
Thanks for the reply. Unfortunately i cannotu use this feature beacuse
i am using .NET 1.1. Is there any other way to do this?

Thanks
Bala

 

[Jeff Dillon]

The client has truly updated 15000 records randomly? Can you describe this
scenario? Perhaps a data acquistion process?

So each record has different update data? So you can't use a set-based
UPDATE MyTable SET....

Jeff

 

[Jeff Dillon]

By the way, GetChanges will put ONLY those records changed into a diffgram

Jeff

 

[Bala Nagarajan]

Hi Jeff,
The 15000 records will have different set of update data, and
each record is to tied to a key column. So i cannot run a single update
statement to update all the records. Hope this helps.
Given this situation any thoughts??
Thanks for the help.

Bala

 

[Jeff Dillon]

Multiple DiffGrams? Post a BCP file to the server, then fire off a SQL Agent
job?

Jeff

 

[Kevin Yu [MSFT]]

Hi Bala,

Sorry, in .NET framework 1.1, there is no ways to do the batch update,
unless you write your own code and SQL stored procedures to achieve this.
Although GetChanges method can get modified rows from a Dataset, the
changes collection is still at the client side. If you want to update them
into the database, the Update method will still go a round trip to the
server for each method in .NET 1.1.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Bala Nagarajan]

Thank you every one for the respone.

I solved the problem by creating a file and sending it to the server.Then i
kicked off a stored procedure from the client to parse the and update the
records. What took 15 minutes now takes just 20-30 seconds for 15000 rows!

Thanks

Bala

 

[Kevin Yu [MSFT]]

Hi Bala,

Nice to hear that you have had the problem resolved. Thanks for sharing
your experience with all the people here. If you have any questions, please
feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)