linq query lock issue (linq to sql)

Question

I got a linq query time out exception. After a bit search online, using TransactionScope to make it 'nolock' gets my vote. However, after using the below code, i still get the same time out exception. Any help is appraicated, thanks in advance.

IEnumerable<IGrouping<string, Log>> grps = logs.GroupBy(l => l.msg_shortdesc);
using (var t = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
  var lst = grps.ToList();
}

Answer 1

You've got the right strategy and statements with TransactionOptions and IsolationLevel.ReadUncommitted to help use NOLOCK in your SQL statements. Hanselman suggests it!

The question comes around to whether the SQL statements generated by LINQ To SQL are performant on the database. Remember that Dev vs. Test vs. Prod will perform differently, depending on the number of rows in your table, and the datatypes within you query.

Some things to check:

• What's the SQL statement being sent to the server? Check with SQL Profiler. Does that SQL run in a timely fashion in an adhoc query via SSMS?

• Is there an index on column msg_shortdesc? Add a new index, if one doesn't exist, on this table for this column. Rerun your LINQ To SQL query to check its performance.

It sounds like you don't have the option of changing the database's configuration (indexes). Suggest that without the ability to make configuration changes, you won't be able to make the 'right' tweaks to improve performance. You'll unfortunately be constantly at the whim of randomness of the load generated by other users.

If you absolutely cannot make an index, consider a caching strategy on this set of data. Perhaps load this data into Session and expire it every n minutes.