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.