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 Server profiler a generic LINQ query

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

took only 19 milliseconds as opposed to a stored procedure

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

(GetMember being the stored procedure) doing the same query which took 100 milliseconds

Why is this?

Edit:

The straight 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 procedure is this

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

So you see the stored procedure query is much simpler.. but yet its slower.... makes no sense to me.

It doesn't look like your two queries do the same thing. The second takes a parameter (presumably to limit the results), where the first is apparently just getting all of the rows. For starters, are you sure the SQL is the same? — Jacob Carpenter, Nov 04 '08 at 19:21
The sql in the stored procedure is the same as what they linq query does...essentially with you are right the parameter... returning all the rows via the linq query is faster than returning a single row in the stored proc. — dswatik, Nov 04 '08 at 19:23

score 3 · Accepted Answer · answered Nov 04 '08 at 19:30

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.

score 1 · Answer 2 · answered Nov 07 '08 at 21:42

1

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 *.

answered Nov 07 '08 at 21:42

HLGEM

94,695
15
113
186

score 1 · Answer 3 · answered Nov 07 '08 at 21:43

1

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

answered Nov 07 '08 at 21:43

HLGEM

94,695
15
113
186

score 0 · Answer 4 · edited May 23 '17 at 12:15

May I add to John Skeet's answer, that when running code several time please remember clean up any query cache.

I can suggest using 'EXPLAIN' with both queries: it seems that MySQL creates query execution plan for a query and SP differently. For SP it complies before substituting parameters with their values, and therefore it does not use indexes, that used in case of hard-coded or substituted parameter. Here is another question about different run times for SP and straight query from SO with query plan data given for both cases.

score 0 · Answer 5 · answered Nov 04 '08 at 19:27

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.

score 0 · Answer 6 · answered Jan 14 '11 at 03:29

0

The * will extend the time it takes to run the query by quite a bit. Also, the straight SQL from LINQ you see in profiler is bracketing ([]) all of the object names - this will trim more time off the query execution time for the LINQ query.

answered Jan 14 '11 at 03:29

Zunandi

164
11

Stored Procedure slower than LINQ query?

6 Answers6