0

I have code like this:

using (var db = new MyDataContext()) {
  db.ExecuteStoreCommand("Set Arithabort on");
  var q = AFairlyComplexQuery(db); // returns an IQueryable<>
  var result = q.ToList();  // Line 4
  return result;
}

I was finding that this query was timing out. I ran SQL Profiler and grabbed the SQL and ran it in SSMS, and it came back in 7 seconds. From past experience, I've learned that this is invariably being caused by the Arithabort option being set to 0, which is why I run that first command. But it's still timing out.

I put a breakpoint on Line 4. When I hit the breakpoint, I went to SSMS, and ran the following query:

SELECT arithabort, * FROM sys.dm_exec_sessions s
WHERE program_name LIKE 'MyProg%'

As expected, Arithabort is set to 1. Then I stepped over Line 4, and immediately went back to SSMS to run that query... and suddenly Arithabort has been set back to 0!

Why? How to fix this?

EDIT: Well, I found a workaround, and it's more or less an answer, but not very satisfying.

using (var db = new MyDataContext()) {
  db.Connection.Open(); // INSERTING THIS LINE PRESERVES ARITHABORT
  db.ExecuteStoreCommand("Set Arithabort on");
  var q = AFairlyComplexQuery(db); // returns an IQueryable<>
  var result = q.ToList();  // Line 4
  return result;
}

I inserted a line db.Connection.Open(). Now Arithabort maintains its original value. But that doesn't explain (a) why this is, and (b) why it's still running roughly 10 times longer than it takes in SSMS...

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • Can it be that your MyDataContext starts a transaction and ends the transaction at Dispose? So your command will be rolled back when something fails. –  May 07 '13 at 14:13
  • @wonko79 - Arithabort is reset before the dispose. – Shaul Behr May 07 '13 at 14:18
  • And you also tried executing the command in an own context and the query in an own context afterwards. –  May 07 '13 at 14:43
  • @wonko79 It seems no matter what I do, even for the simplest Linq queries, Arithabort is implicitly reset to 0. Tested in the simplest of tests, and behavior is consistent. – Shaul Behr May 07 '13 at 14:49

1 Answers1

1

It seems to be a known issue that EF sets Arithabort off. And for some reason, ExecuteStoreCommand doesn't work, while the following code does work:

var cmd = ((EntityConnection)db.Connection).StoreConnection.CreateCommand();
cmd.Connection.Open();
cmd.CommandText = "set arithabort on";
cmd.ExecuteNonQuery();

See this answer to this question for more.

Community
  • 1
  • 1
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387