Hello all,
If my program was to hit the database with multiple updates would it be better to pull in the tables into a dataset, change the values and then send it back to the database. Does anyone know what's more expensive?
A:
Depends on the size of the DataSet. If your data set is too large, it doesn't worth it. Otherwise, it might be a good approach. However, nothing prevents you to do multiple updates in a batch even without using a DataSet. You could write a stored procedure with an XML parameter that will do batch updates for you.
Mehrdad Afshari
2009-01-09 19:30:08
+1
A:
No matter what, the database needs to perform all those updates based on the edits you did to the local DataSet. As I understand it, that will be just as expensive as sequentially updating. The only advantage is its easier to iterate over a dataset rather than pull-and-push one result after another.
Soviut
2009-01-09 19:34:58
Soviut, DataSet class supports batch updates. There will be a single round trip to database.
Mehrdad Afshari
2009-01-09 19:44:08
(Not counting exception handling).
le dorfier
2009-01-09 19:59:49
+1
A:
What will be expensive is all the workaround code for dealing with the potential exceptions that can occur because you choose "which costs less" over "which is simplest". Premature optimization.
le dorfier
2009-01-09 19:38:40
Agreed. However it might not be a minor thing, specially if DB bandwidth is a problem.
Mehrdad Afshari
2009-01-09 19:45:13
Then optimize it at the right time, when the decision info is available.
le dorfier
2009-01-09 19:58:27
A:
Question: what do you mean "more expensive"? More expensive in terms of execution time, say? More expensive in terms of development or maintenance?
Mark A Johnson
2009-01-09 21:47:36
Sorry, I meant execution time. Its the same number of updates. Sure, some DB's may have marginal performance differences for one big sql statement versus several smaller ones, but unless you're doing thousands of records a second, you may never see this difference.
Soviut
2009-01-09 22:08:46