Updating multiple records

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
Fri May 05 14:25:26 CDT 2006

DiffGrams?

"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:%23ptfEvGcGHA.3840@TK2MSFTNGP04.phx.gbl...
> 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
>
>

Bala
Fri May 05 14:31:04 CDT 2006

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

Thanks
Bala
"Jeff Dillon" <jeffdillon@hotmail.com> wrote in message
news:u807omHcGHA.3484@TK2MSFTNGP03.phx.gbl...
> DiffGrams?
>
> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
> news:%23ptfEvGcGHA.3840@TK2MSFTNGP04.phx.gbl...
>> 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
Fri May 05 14:39:39 CDT 2006

Did you look? Google "diffgrams oracle"

"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:uGDS0pHcGHA.1264@TK2MSFTNGP05.phx.gbl...
> Jeff,
> Thanks for replying. Can you please elaborate on this?
>
> Thanks
> Bala
> "Jeff Dillon" <jeffdillon@hotmail.com> wrote in message
> news:u807omHcGHA.3484@TK2MSFTNGP03.phx.gbl...
>> DiffGrams?
>>
>> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
>> news:%23ptfEvGcGHA.3840@TK2MSFTNGP04.phx.gbl...
>>> 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
>>>
>>>
>>
>>
>
>

Cowboy
Fri May 05 20:13:57 CDT 2006

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!
*************************************************
"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:%23ptfEvGcGHA.3840@TK2MSFTNGP04.phx.gbl...
> 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
>
>

v-kevy
Sun May 07 21:40:51 CDT 2006

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
Mon May 08 09:45:54 CDT 2006

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


"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
> 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.)
>

Jeff
Mon May 08 11:06:21 CDT 2006

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

Jeff

"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:eBYod4qcGHA.3936@TK2MSFTNGP05.phx.gbl...
> 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
>
>
> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
>> 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.)
>>
>
>

Jeff
Mon May 08 11:05:51 CDT 2006

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

"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:eBYod4qcGHA.3936@TK2MSFTNGP05.phx.gbl...
> 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
>
>
> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
>> 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
Mon May 08 13:42:44 CDT 2006

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" <jeffdillon@hotmail.com> wrote in message
news:%23Ax4HlrcGHA.2068@TK2MSFTNGP02.phx.gbl...
> 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
>
> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
> news:eBYod4qcGHA.3936@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
>> news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
>>> 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.)
>>>
>>
>>
>
>

Jeff
Mon May 08 16:19:09 CDT 2006

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

Jeff

"Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
news:%23REey8scGHA.1276@TK2MSFTNGP03.phx.gbl...
> 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" <jeffdillon@hotmail.com> wrote in message
> news:%23Ax4HlrcGHA.2068@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
>> news:eBYod4qcGHA.3936@TK2MSFTNGP05.phx.gbl...
>>> 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
>>>
>>>
>>> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
>>> news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
>>>> 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.)
>>>>
>>>
>>>
>>
>>
>
>

v-kevy
Mon May 08 22:02:33 CDT 2006

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
Tue May 09 15:00:59 CDT 2006

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

"Jeff Dillon" <jeffdillon@hotmail.com> wrote in message
news:e5p$QUucGHA.4576@TK2MSFTNGP05.phx.gbl...
> Multiple DiffGrams? Post a BCP file to the server, then fire off a SQL
> Agent job?
>
> Jeff
>
> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
> news:%23REey8scGHA.1276@TK2MSFTNGP03.phx.gbl...
>> 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" <jeffdillon@hotmail.com> wrote in message
>> news:%23Ax4HlrcGHA.2068@TK2MSFTNGP02.phx.gbl...
>>> 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
>>>
>>> "Bala Nagarajan" <baladotnet@newsgroups.nospam> wrote in message
>>> news:eBYod4qcGHA.3936@TK2MSFTNGP05.phx.gbl...
>>>> 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
>>>>
>>>>
>>>> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
>>>> news:criqWjkcGHA.4776@TK2MSFTNGXA01.phx.gbl...
>>>>> 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.)
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

v-kevy
Tue May 09 21:45:20 CDT 2006

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.)