LINQ calling a Stored Procedure is slow

Question

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

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?

The following is the SQL send from LINQ:

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:

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.

Thanks for the help.

SOLUTION:

SET ARITHABORT ON;

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

Answer 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

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/

Answer 2

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

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