Linq and SqlBulkCopy performance

[Snorlaks]

Hello everyone,

Im facing very irritating problem. I use LINQ to get data from sql server then I do sth with it create new objects and I want to insert thos newly created objects into database.

There is over 130 000 000 records that I have to proceed. In first iteration I take 10000 records and process them and In result I have new sth like 7000 new records that I need to insert to different table.

Processing this records takse sth like 0,03 s. And there is the problem. Inserting this data using DataContext.SubmitChanges takes over 12 s.

Using SqlBulkCopy doesnt help while it takes ~17 s to to the same.

If I'm lucky It would take 39 h to proceed all 130 000 000 records and insert output to table in database.

Is there aby other way I can make performance better ?

Thanks for help

[Shuja Ali]

I would rather use Sql Server Integration Services package to perform such kind of heavy data loads. Writing code for it and then processing 130M records would definitely take time. SSIS has been built keeping that in mind and don't forget SSIS is written on top of .NET framework so you can most of the things in SSIS that you could do in ADO.NET.

[Snorlaks]

To be honest I don't know anything about Integration Services. Is it possible to use it from WinForms application that I have to develop?

[Shuja Ali]

As I mentioned earlier, it is not a good idea to have such huge data loads in a Win Form/ASP.NET application. If you are familiar with SQL Server, Integration Services would be easy to learn. There is never a bad time to learn something new.