-
November 9th, 2009, 07:27 AM
#1
Linq and SqlBulkCopy performance
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
-
November 9th, 2009, 02:45 PM
#2
Re: Linq and SqlBulkCopy performance
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.
-
November 9th, 2009, 04:14 PM
#3
Re: Linq and SqlBulkCopy performance
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?
-
November 10th, 2009, 02:02 PM
#4
Re: Linq and SqlBulkCopy performance
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|