3

I'm inserting approximently 3 million records in a database using this solution. Eventually when the application has been inserting records for a while (my last run lasted around 4 hours), it gives a timeout with the following SqlException:

"SqlExcepetion: Timeout expired. The timeoutperiod elapsed prior to completion of the operation or the server is not responding."

What's the best way to handle this exception? Is there a way to prevent this from happening or should I catch the exception?

Thanks in advance!

Community
  • 1
  • 1
Bas
  • 1,232
  • 1
  • 12
  • 26

3 Answers3

10

If everything you have is a hammer, every problem looks like a nail.

Seriously, even TRYING to use Linq2SQL for inserting 3 million records is a broken approach. ORM's are a lot of good things, they are NOT bulk insert elements.

I suggest:

  • Generate the table data into a file
  • Load the file using appropriate tools (bulk load mechanism). You wil lbe surprised about the performance (hint: I load about 600 million records in 15 minutes);)

Bulk load has a lot of performance advantages. Naturally assuming you talk of something like an ETL load process here - but I fail to see a 3 million load as anything except a data warehouse / reporting / archiving style of load operation, it definitlely and per definition is not real time transaction processing ;)

Use the right tool for the job.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • I believe the expression is "If your only tool is a hammer..." – si618 May 03 '10 at 11:27
  • Whatever the expression is, it's an awesome explanation! +1 for this awnser, win! – Bas May 03 '10 at 11:31
  • 2
    In addition to TomTom's answer have a look at the SqlBulkCopy class: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx – Jakob Christensen May 03 '10 at 11:40
  • In an app I am building, I am adding mere 10's of thousands of records, not millions, and linq2sql was killing me. SqlBulkCopy made a huge different, and I was able to build it into my data layer class along side the linq2sql with no difficulty. – Mike Jacobs May 03 '10 at 14:48
3

Generate a SQL script (or string), and execute that directly.

L2SQL is not made for mass operations like this.

leppie
  • 115,091
  • 17
  • 196
  • 297
2

I think you've to increase the CommandTimeout of your command.

Solution can be found here

Amsakanna
  • 12,254
  • 8
  • 46
  • 58