troubleshooting Question

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

Avatar of LovinSpoonful
LovinSpoonful asked on
Visual Basic.NET
6 Comments3 Solutions1643 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros