CODEWITHSUNDEEP
linq-to-sqlstored-procedures
dswatik
dswatik

Reputation: 9209

Stored Procedure slower than LINQ query?

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.

Upvotes: 3

Views: 1908

Answers (6)

Stoleg
Stoleg

Reputation: 9300

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.

Upvotes: 0

Zunandi
Zunandi

Reputation: 164

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.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96570

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96570

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

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1500855

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.

Upvotes: 3

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

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.

Upvotes: 0

Related Questions