存储过程比LINQ查询慢？

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

我正在进行一些测试,直接LINQ-to-SQL查询运行速度比通过LINQ查询调用存储过程快至少80%

In SQL Server profiler a generic LINQ query

在SQL Server探查器中是一个通用的LINQ查询

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

took only 19 milliseconds as opposed to a stored procedure

与存储过程相比,只花了19毫秒

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

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

(GetMember是存储过程)执行相同的查询,耗时100毫秒

Why is this?

为什么是这样?

Edit:

The straight LINQ looks like this in Profiler

直线LINQ在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.

所以你看到存储过程查询要简单得多..但它的速度慢......对我来说毫无意义。

6 个解决方案

#1

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.

1)比较喜欢。在两种情况下执行完全相同的操作,而不是在一种情况下获取所有值并在另一种情况下执行查询。

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.

2)不要只执行一次代码 - 多次执行,因此优化器有机会工作并避免一次性性能命中。

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.

3)使用分析器(好吧,一个在.NET端,一个在SQL端)来找出性能实际上在哪里不同。

#2

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

可能使它变慢的一件事是select *。通常,如果指定了列,查询会更快,特别是如果LINQ查询未在查询中使用所有可能的列,则它将比select *更快。

#3

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

我忘记了,proc也可能有参数嗅探问题。

#4

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.

如果您想尝试确定性能,则需要比较SAME查询,使用相同的值等。

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

此外,您可以尝试使用LinqPad来查看生成的SQL,以便潜在地识别导致响应缓慢的区域。

#5

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.

*将延长运行查询所花费的时间。此外,您在探查器中看到的LINQ中的直接SQL是对所有对象名称进行包围([]) - 这将减少LINQ查询的查询执行时间。

#6

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

我可以添加John Skeet的答案,在运行代码几次时请记住清理任何查询缓存。

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.

我建议对两个查询使用'EXPLAIN':似乎MySQL为查询和SP创建查询执行计划的方式不同。对于SP,它在用参数替换参数之前符合,因此它不使用在硬编码或替换参数的情况下使用的索引。这是关于SP的不同运行时间和来自SO的直接查询的另一个问题,其中给出了针对两种情况的查询计划数据。

#1