LINQ calling a Stored Procedure is slow

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?

  • How to connect to SQL Server database using windows authentication remotely?
  • Query Index for Dictionary Based Queries
  • Row_number over partition and find the max rn value
  • SQL Server CE 3.5 update row error DB_E_ERRORSOCCURRED column error is DBSTATUS_E_SCHEMAVIOLATION
  • Show Row Count in SQL Profiler
  • Dynamic SQL Query Search
  • 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.

  • How to set monday as first day of week in SQL Server
  • ADO error 16389 with local access SQL Server, database connection failing when using wireless card
  • How to check for database availability
  • Log table Schema changes
  • What is the limitation in the length of an SqlCommand query
  • In SQL Server, why is it that NULL does not equal empty string AND doesn't not equal empty string?
  • 2 Solutions collect form web for “LINQ calling a Stored Procedure is slow”

    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/

    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);
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.