How to bulk update / insert / add rows to a datatable without writing to the database

LovinSpoonful
LovinSpoonful used Ask the Experts™
on
I have a large SQL Server database and run a complex transformation sequence against the data.  This requires a very long series of sql bulk updates, inserts, adds, etc.  This process takes 3-4 minutes to run.  Most of that time is wasted on disk i/o as each sql statement updates real tables in SQL Server.  I am trying to find a way to do 'sql-like' bulk updates in memory (for instance using a cached SqlDataAdapter dataset).  

I looked at LINQ but it doesn't support bulk (set-based) updates.  I need this process to run in 30 seconds, not 3 minutes.  I don't need to update the tables in the sql database until the process is complete.

Is there a way to do all the transformation within data containers that are in RAM, then finally export the final results to SQL server ?  I am talking about 100's of transforms, not a few.  And NO I cannot use a for/each loop to update each row individually - that would take much longer to run.

Could you please submit actual code snippetts, not general advice, if you have a method to do this.  Thanks in advance for your assistance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
Commented:
If you are working with a SqlDataAdapter for your updates, you can set the UpdateBatchSize property of the DataAdapter to something else than the default of 1. 0 is quite interesting since it sets the size to the maximum numbers that the server accepts in one shot. Your updates are then batched in only one call to the database instead of being sent one by one. This often have a great impact on performance.

Another way to go might be to wrap your commands under a transaction. Because of the way transactions are implemented on the server, they also often spees up things quite a lot. I remember a case a few years ago where we were synchronizing and Access database with a SQL Server database. It took between 2 to 10 hours (a few years ago, things were slower than they are today) to complete. Putting the whole operation under a transaction, which took only a few lines of code, brought back the thing to under 20 minutes in the worst cases.

Lookup the documentation for the SqlTransaction class. Be aware though that by default, transactions lock all the records that are touched while the transaction is running. That might freeze everybody if the transaction touch too many records for too long a time. In my experience stated in the last paragraph, the operations were done at night, in between shifts, so the 20 minutes it took did not have any impact on the workers.

Author

Commented:
Thanks for your comment.  What you are describing above is writeback to the SQL Server database, correct?  

What I am looking for is a method to bulk update something in memory.  For instance, I have a dataset with 5 tables and I want to denormalize the data, passing info from one table to another.  I would like to do this within the dataset without writing to the sql server database, so that it would be faster.

Or, I would like to know how to run a bulk update query with a join on an Array, Dictionary, List, or other data object so that one 'table' (array) of information stored in memory could be joined to another 'table' and data passed between them.

I do not want to update the sql database because this involves writing data to disk.  I am looking for something faster than writing to the sql database.  I already have a method for doing bulk updates very quickly to the database, but this is not 'quick enough' when you have a few hundred of them and you need to get it all done in seconds instead of minutes.


Thanks again for your comment and let me know if I am missing the gist of your suggestion.


Top Expert 2015
Commented:
The way they make us do such thing in .NET is LINQ. Has you have found, it is limited in many ways. Personnally, I have always found LINQ to be crap, and now that Microsoft is putting the emphasis on Data Entities, it is proof that even them do not find LINQ to be such the innovation they tried to sold us in 2008.

For performance, maintenance and in order to have more control, I do most of my work in my own classes, using ADO only to fill my own collections and send back the changed to the database. It's a lot more work in the start, but once it is done, it is almost a pleasure to maintain. And I do not have to maintain 4 technologies in my applications because Microsoft change it's mind every 2 or 3 years (Typed DataSet -> LINQ -> Data Entities).

Because of that I have no experience with Data Entities, but maybe you can find what you are looking for in them. Good luck.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
Looks like Data Entities also requires you to make bulk update statements on the sql server database; encourages you to use Stored Procedures to do so.

Well, perhaps what I'm looking for does not exist.  I just thought you could query arrays like you can query tables, but perhaps not.

Thanks
The solution I eventually adopted was to use SQL Lite, an in-memory database.

Author

Commented:
Thanks guys for your assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial