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?

Answer 1

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?

Answer 2

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.