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.

Which one did you execute first? SSMS or the application? Can you repeatedly generate the same performance results when toggling between the two query methods? Where exactly are you getting the metrics you cited? — Pete M, Apr 05 '11 at 15:11
@Pete M, thank you for replying, I called via LINQ first, and copied the SQL into Query manager and executed it. But the result is consistantly slow from LINQ, even after the first call. The measurements come from the profiler, shipping with SQL server 2008. — Jesper Fyhr Knudsen, Apr 05 '11 at 15:14
Bummer, I was hoping it was going to be an execution plan issue... Just to be clear, you're seeing 1400ms execution time entirely within SQL Server? As in, not including transport/presentation/other? Do you have LINQPad? I'd try referencing your DataContext and running the query outside the context of your application and see if you get the same results. It's really strange to see that kind of difference entirely server side, and consistently over multiple hits, on a stored procedure no less... — Pete M, Apr 05 '11 at 16:17
Could it be because of parameter sniffing? In ssms try first running this line before you execute the stored procedure and tell us what you get. SET ARITHABORT ON; — ingo, Apr 05 '11 at 21:29
First thing that comes to my mind is parameter sniffing, like @ingo mentioned. Check this question: http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server — rsbarro, Apr 06 '11 at 03:39
Thank you, will check it out later and report back. @Pete, yes the 1400ms is from the SQL profiler, so it's entirely inside the database server, I don't know if there is anything that can influence something like that on the application side. I don't have LINQPad, but will look into it, thanks. — Jesper Fyhr Knudsen, Apr 06 '11 at 09:18
@ingo, SET ARITHABORT ON; solved it, I've set it in the Stored Procedure, is that the correct way to go about it? Leave an answer, and I'll accept. Everyone else thanks for the help. — Jesper Fyhr Knudsen, Apr 06 '11 at 11:20
Here is the blog post I always refer to when it come sto parameter sniffing. http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx — tster, Apr 06 '11 at 11:24

score 11 · Accepted Answer · answered Apr 06 '11 at 16:07

11

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/

answered Apr 06 '11 at 16:07

ingo

5,469
1
24
19

1

Glad to be of help. I remember scratching my head on this one for a full day. Would have been nice to know of stackoverflow back then :) – ingo Apr 08 '11 at 00:38
Great link to some extensive research into the problem, but there's so much there that you need A) at least an hour to read through it and digest it or B) 15 or 20 minutes to skim it and try to find the part that will help you fix your problem. I went for the latter approach, but the problem recurred even after running sp_recompile (which the author of the link above says will be the likely case). In my case, what finally fixed my problem was to swap out the datetime parameters that I was passing into to my sproc for varchar parameters (and then I converted them to datetime within the sproc). – Jagd Jun 20 '13 at 20:30

score 2 · Answer 2 · answered Feb 20 '13 at 20:02

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

LINQ calling a Stored Procedure is slow

2 Answers2

Linked