LovinSpoonful
asked on
How to bulk update / insert / add rows to a datatable without writing to the database
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys for your assistance.
ASKER
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.