Batch update in SQL Server Database
-
Hello all, I have a windows application which updates multiple rows (using a stored procedure in the db) in a SQL Server database, but this updates run many times sequentially in a for loop. For the update methos I have tried the following methods: - The SqlCommand object running my stored procedure as a text commnand and executing it with the ExecuteNonQuery method. - An SqlDataAdapter using its UpdateCommand attribute and executing using the ExecuteNonQuery method. In both ways the running time is really slow. Can anyone suggest me an other way to do the updates. It would be good if I could save my updates n a dataset or a datatable and after save it to the db. Thanks in Advance Dimitris
-
Hello all, I have a windows application which updates multiple rows (using a stored procedure in the db) in a SQL Server database, but this updates run many times sequentially in a for loop. For the update methos I have tried the following methods: - The SqlCommand object running my stored procedure as a text commnand and executing it with the ExecuteNonQuery method. - An SqlDataAdapter using its UpdateCommand attribute and executing using the ExecuteNonQuery method. In both ways the running time is really slow. Can anyone suggest me an other way to do the updates. It would be good if I could save my updates n a dataset or a datatable and after save it to the db. Thanks in Advance Dimitris
Are you just updating rows, or are you inserting new ones and deleting them too? One approach you might consider is rather than repeatedly calling an update proc, instead insert each modification into a temporary table and then call a proc which would do the update in one single atomic shot. The advantage of this would be that you could bulk load that temporary table really quickly. If that's too much, have you 'prepared' the proc so that you just change the value of the SqlParameters rather than create new ones each time?
Regards, Rob Philpott.
-
Are you just updating rows, or are you inserting new ones and deleting them too? One approach you might consider is rather than repeatedly calling an update proc, instead insert each modification into a temporary table and then call a proc which would do the update in one single atomic shot. The advantage of this would be that you could bulk load that temporary table really quickly. If that's too much, have you 'prepared' the proc so that you just change the value of the SqlParameters rather than create new ones each time?
Regards, Rob Philpott.
Thank you Rob, Actually my stored procedure has a double functionality. Updates rows in my table (depending on a key) and in case of non existing rows it inserts them. I haven't tried to bulk insert in a temp table and after update mine because I have to do this many times and I'm afraid that it would be realy time consuming to do this every time, because every time I should delete the temp table. That's why I would like to fill somehow a datatable in the memory and after my insert/update queries (maybe without using stored procedure) to update this in the db. Thanks again, Dimitris
-
Hello all, I have a windows application which updates multiple rows (using a stored procedure in the db) in a SQL Server database, but this updates run many times sequentially in a for loop. For the update methos I have tried the following methods: - The SqlCommand object running my stored procedure as a text commnand and executing it with the ExecuteNonQuery method. - An SqlDataAdapter using its UpdateCommand attribute and executing using the ExecuteNonQuery method. In both ways the running time is really slow. Can anyone suggest me an other way to do the updates. It would be good if I could save my updates n a dataset or a datatable and after save it to the db. Thanks in Advance Dimitris
I use a parameterized statement with ExecuteNonQuery, usually with a transaction.
-
Thank you Rob, Actually my stored procedure has a double functionality. Updates rows in my table (depending on a key) and in case of non existing rows it inserts them. I haven't tried to bulk insert in a temp table and after update mine because I have to do this many times and I'm afraid that it would be realy time consuming to do this every time, because every time I should delete the temp table. That's why I would like to fill somehow a datatable in the memory and after my insert/update queries (maybe without using stored procedure) to update this in the db. Thanks again, Dimitris
I wouldn't be too concerned about the overhead of creating a table each time, this would be far more effecient than multiple calls to an update proc. AFAIK, there isn't really a way to bulk update a database, but you can bulk insert into it very quickly. So, if you did want to try this approach, create a new prepare proc which creates the table, use the
SqlBulkCopyclass to rapidly write to this, and then call a commit proc which updates and inserts based on this table and deletes it afterwards.Regards, Rob Philpott.
-
I wouldn't be too concerned about the overhead of creating a table each time, this would be far more effecient than multiple calls to an update proc. AFAIK, there isn't really a way to bulk update a database, but you can bulk insert into it very quickly. So, if you did want to try this approach, create a new prepare proc which creates the table, use the
SqlBulkCopyclass to rapidly write to this, and then call a commit proc which updates and inserts based on this table and deletes it afterwards.Regards, Rob Philpott.