Databases
StackOverflow2013
Postsdbo
POLINQ calling a Stored Procedure is slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ calling a Stored Procedure is slow
    primarykey
    data
    text
    <p>Everything is relative of course, but compared to simply executing the same SQL using the query manager, there is a big difference.</p> <p>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?</p> <p>The following is the SQL send from LINQ:</p> <pre><code>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 </code></pre> <p>The .Net C# code is simply:</p> <pre><code>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) } </code></pre> <p>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.</p> <p>Thanks for the help.</p> <p><strong>SOLUTION</strong>:</p> <pre><code>SET ARITHABORT ON; </code></pre> <p>So it was not a LINQ problem, but more of a general SQL Server issue.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJesper Fyhr Knudsen
    1. USJesper Fyhr Knudsen
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. USBigwave
      1. VTFavorite
    1. COWhich 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?
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. USPete M
    2. CO@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.
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. USJesper Fyhr Knudsen
    3. COBummer, 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...
      singulars
      1. POLINQ calling a Stored Procedure is slow
      1. USPete M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload