batch updates vs single sp call

[Guest]

Which of the ff scenarios is better in doing batch updates made to a dataset?

Scenario #1: Use a single stored procedure call and pass a string parameter containing a concatenated set of rows (separated by a defined delimiter)

Scenario #2: Use ADO.NET DataAdapter.Update() to do batch updates. (Although automated, the DataAdapter will loop through each modified or new row and call the corresponding stored procedure for a given SQL command/action. In effect, there are multiple SP calls during the batch update.

Appreciate comments/recommendations ... Thanks.

 

[Pavel KOHOUT]

hi, i recommend you create custom commands for your dataadapter. Theese
command could be a storeprocedury type... so when you call update method
your dataadapter will call your stored procedures (thru dataapter-commands).
Your first scenario is fine but i think for another usage...


--

Pavel KOHOUT
Advantage Solutions, Ltd.
www.advantages.cz

Which of the ff scenarios is better in doing batch updates made to a dataset?:

Scenario #1: Use a single stored procedure call and pass a string

parameter containing a concatenated set of rows (separated by a defined
delimiter).

Scenario #2: Use ADO.NET DataAdapter.Update() to do batch updates.

(Although automated, the DataAdapter will loop through each modified or new
row and call the corresponding stored procedure for a given SQL
command/action. In effect, there are multiple SP calls during the batch
update.)

 

[William \(Bill\) Vaughn]

The SP won't be able to deal with the row values as ADO.NET Rows, but you
might be able to send in a delimited string. In this case the SP would have
to parse the string and build the commands on its end and execute them. This
would save a round trip, but it would also require quite a bit of
server-side processing.
I would continue to use the round-trip Update used by ADO.NET today as it's
going to be enhanced in the 2005 version with batch operations.
Actually, if you're doing bulk data loading, I would use BCP or DTS.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Which of the ff scenarios is better in doing batch updates made to a dataset?:

Scenario #1: Use a single stored procedure call and pass a string

parameter containing a concatenated set of rows (separated by a defined
delimiter).

Scenario #2: Use ADO.NET DataAdapter.Update() to do batch updates.

(Although automated, the DataAdapter will loop through each modified or new
row and call the corresponding stored procedure for a given SQL
command/action. In effect, there are multiple SP calls during the batch
update.)