LINQ调用存储过程很慢

Everything is relative of course, but compared to simply executing the same SQL using the query manager, there is a big difference.

当然，一切都是相对的，但与使用查询管理器简单地执行相同的SQL相比，存在很大的差异。

I've used the profiler to see what SQL statements the database execute when LINQ calls a stored procedure. The result is returned in about 1400ms, if I copy/paste the SQL and run the exact same SQL through the query manager, the result is returned in 2ms. This make me wonder if there is something I need to do? Are anyone here have had similar experiences?

我使用分析器来查看LINQ调用存储过程时数据库执行的SQL语句。结果在大约1400ms内返回，如果我复制/粘贴SQL并通过查询管理器运行完全相同的SQL，结果将在2ms内返回。这让我想知道我有什么需要做的吗？这里有人有类似的经历吗？

The following is the SQL send from LINQ:

以下是LINQ的SQL发送：

declare @p26 int
set @p26=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[TapeInfo_Get] @TapeFlag_IsDigitized = @p0, @TapeFlag_ChosenSingleTape = @p1, @TapeFlag_ChosenHierarchy = @p2, @TapeFlag_ChosenForced = @p3, @TapeFlag_ExcludedHierarchy = @p4, @TapeFlag_ExcludedARKBNR = @p5, @TapeFlag_ExcludedForced = @p6, @TapeFlag_ExcludedFilmRoll = @p7, @TapeFlag_ExcludedDVCPRO = @p8, @TapeFlag_ExcludedVHS = @p9, @TapeFlag_ExcludedType = @p10, @TapeFlag_NoticeBNR = @p11, @TapeFlag_NoticeMultiplePNR = @p12, @TapeFlag_NoticeType = @p13, @ProductionFlag_ExcudedDate = @p14, @ProductionFlag_NoticeMultipleTape = @p15, @ProductionFlag_NoticeFilm1C = @p16, @ProductionFlag_NoticeFilmBetaDigial = @p17, @ProductionFlag_ExcludedForeignProd = @p18, @Query = @p19, @PageIndex = @p20, @PageSize = @p21, @ReturnCount = @p22',N'@p0 bit,@p1 bit,@p2 bit,@p3 bit,@p4 bit,@p5 bit,@p6 bit,@p7 bit,@p8 bit,@p9 bit,@p10 bit,@p11 bit,@p12 bit,@p13 bit,@p14 bit,@p15 bit,@p16 bit,@p17 bit,@p18 bit,@p19 varchar(8000),@p20 int,@p21 int,@p22 bit,@RETURN_VALUE int output',@p0=0,@p1=1,@p2=1,@p3=1,@p4=0,@p5=0,@p6=0,@p7=0,@p8=0,@p9=0,@p10=0,@p11=0,@p12=0,@p13=0,@p14=0,@p15=0,@p16=0,@p17=0,@p18=0,@p19=NULL,@p20=0,@p21=10,@p22=0,@RETURN_VALUE=@p26 output
select @p26

The .Net C# code is simply:

.Net C＃代码很简单：

using( BRSDataContext dc = new BRSDataContext() )
{
    dc.TapeInfo_Get(false, false, false, false, false, false, false, false, false, false, false, null, true, null, false, null, null, null, false, query, startRowIndex, count, false)
}

Is there something I'm missing? Any ideas what can influence the performance so dramatically? The database (MSSQL 2008) and the webserver hosting the asp.net site that executes the LINQ, are located on the same network and are both running Windows server 2008 std 32bit.

有什么我想念的吗？什么能够如此戏剧性地影响性能？数据库（MSSQL 2008）和托管执行LINQ的asp.net站点的网络服务器位于同一网络上，并且都运行Windows server 2008 std 32bit。

Thanks for the help.

谢谢您的帮助。

SOLUTION:

解：

SET ARITHABORT ON;

So it was not a LINQ problem, but more of a general SQL Server issue.

所以它不是LINQ问题，而是更多的一般SQL Server问题。

2 个解决方案

#1

Set arithabort on; is only to test it. There are several recommended ways to fix this issue. One is to add "with recompile" to the stored procedure. But I usually fix it by not using the input parameters directly

设置arithabort;只是为了测试它。有几种推荐的方法可以解决此问题。一种是在存储过程中添加“with recompile”。但我通常不直接使用输入参数来修复它

ex:

例如：

create stored procedure foo( @ParamUserId int)
as
   declare @UserId int
   set @UserId = @ParamUserId

   select * from Users where UserId = @UserId

Or something like that.

或类似的东西。

Here is a good article on the matter http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

这是一篇关于此事的好文章http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

#2

Here is the C# for linq to Set arithabort on;

这是设置arithabort的linq的C＃;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(myConnectionString);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("set arithabort on;", conn);
command.Connection.Open();
command.ExecuteNonQuery();
CMyDataContext myDataContext = new CMyDataContext(conn);

#1