Stored Procedure slower than LINQ query?

Question

I was doing some testing and straight LINQ-to-SQL queries run at least 80% faster than if calling stored procedures via the LINQ query

in SQL profiler a generic LINQ query

 var results = from m in _dataContext.Members
 select m;

took only 19 miliseconds as apposed to a stored procedure

 var results = from m in _dataContext.GetMember(userName)
 select m;

(GetMember being the stored proc) doing the same queiry which took 100 miliseconds

Why is this?

Edit: The staight LINQ looks like this in Profiler

SELECT [t1].[MemberID], [t1].[Aspnetusername], [t1].[Aspnetpassword], [t1].[EmailAddr], [t1].[DateCreated], 
[t1].[Location], [t1].[DaimokuGoal], [t1].[PreviewImageID], [t1].[value] AS [LastDaimoku], [t1].[value2] AS 
[LastNotefied], [t1].[value3] AS [LastActivityDate], [t1].[IsActivated]
FROM (
    SELECT [t0].[MemberID], [t0].[Aspnetusername], [t0].[Aspnetpassword], [t0].[EmailAddr], [t0].[DateCreated], 
[t0].[Location], [t0].[DaimokuGoal], [t0].[PreviewImageID], [t0].[LastDaimoku] AS [value], [t0].[LastNotefied] AS 
[value2], [t0].[LastActivityDate] AS [value3], [t0].[IsActivated]
    FROM [dbo].[Members] AS [t0]
    ) AS [t1]
WHERE [t1].[EmailAddr] = @p0

The stored proc is this

SELECT     Members.*
FROM         Members 
                      WHERE dbo.Members.EmailAddr = @Username

So you see the stored proc query is so much more simple.. but yet its slower....makes no sense to me.

Answer 1

A noted in the comments some of this is that you are not comparing apples to apples. You are trying to compare two different queries, thus getting different results.

If you want to try and determine performance you would want to compare the SAME queries, with the same values etc.

Also, you might try using LinqPad to be able to see the generated SQL to potentially identify areas that are causing slowness in response.

Answer 2

1) Compare like with like. Perform exactly the same operation in both cases, rather than fetching all values in one case and doing a query in another.

2) Don't just execute the code once - do it lots of times, so the optimiser has a chance to work and to avoid one-time performance hits.

3) Use a profiler (well, one on the .NET side and one on the SQL side) to find out where the performance is actually differing.

Answer 3

One thing that might make it slower is the select *. Usually a query is faster if columns are specified, And in particular if the LINQ query is not using all the possible columns inthe query, it will be faster than select *.

Answer 4

I forgot, the proc could also have parameter sniffing issues.