What causes Timeout expired SqlExceptions in LINQ to SQL?

Question

My application keeps running into Timeout Expired SqlExceptions. The thing is that this query is one that will simply have to run for a decent amount of time. I'm having trouble figuring out where this exception is occurring though. Is this a timeout that's created at the database server or is it happening in my program? Or if it could be both, how do I figure out which one it is?

And lastly, how do I extend the timeout period?

Softboiled

Answer 1

It is likely that you are running over the CommandTimeout set on your DataContext. This defaults to 30 seconds.

All you need to do is set the property on the DataContext before you execute your query.

Continual

And how am I supposed to that? – Dearman

Answer 2

increase timeout = BAD
fix query = GOOD

I'm not sure of all the details involved, but in general the followng applies:

when a query runs slow do the following in management studio:

• run SET ShowPlan_All ON
• run your query
• look at the output for the word "scan". your problem is there.

"Scan" = touch each row (table or index). Would you like to "scan" a phone book looking for one number or use the index?

Laundress

We've tuned the query about as much as we can. Unfortunately, there's only so much you can do to speed up the process of pulling 15k records out of a table of 2 million records through a linked server. – Softboiled

edit your question and include the output from SET ShowPlan_All ON – Laundress

@Jason Baker, I have many queries that join on multiple tables with more rows than your 2 million, and they run a lot faster than 30 seconds. You say "We've tuned the query about as much as we can", and that is why I was offering my help. – Laundress

Answer 3

This is probally occuring becuase the default timeout is 30 seconds. You can change this by setting a Timeout attribute on the connection string. Alternatley you can set this on the DataContext.

Here's an article on code project about this.

Gotama