为什么Linq查询将我的Arithabort选项设置为false? [英] Why is Linq query setting my Arithabort options to false?

问题描述

我有这样的代码:

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;
}

我发现此查询正在超时.我运行了SQL事件探查器，并抓住了SQL并将其在SSMS中运行，它在7秒钟后又回来了.从过去的经验中，我了解到这总是由 Arithabort 选项设置为 0 引起的，这就是为什么我运行第一个命令的原因.但它仍在超时.

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.

我在第4行上设置了一个断点.当我碰到断点时，我去了SSMS，并运行了以下查询:

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%'

如预期的那样， Arithabort 设置为 1 .然后，我越过4号线，立即返回SSMS运行该查询...，然后 Arithabort 突然被设置回 0 ！

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!

为什么?该如何解决?

编辑:嗯，我找到了一种解决方法，它或多或少是一个答案，但并不十分令人满意.

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;
}

我插入了一行 db.Connection.Open().现在， Arithabort 保持其原始值.但这并不能解释(a)为什么这样做，以及(b)为什么它仍然比SSMS的运行时间长大约10倍...

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...

推荐答案

这似乎是EF将 Arithabort 设置为关闭的已知问题.出于某种原因， ExecuteStoreCommand 不起作用，而以下代码却起作用:

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.

这篇关于为什么Linq查询将我的Arithabort选项设置为false?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！