为什么实体框架生成缓慢过度设计的SQL？ [英] Why does Entity Framework generate slow overengineered SQL?

问题描述

我有这个code：

DbSet<TableName> table = ...// stored reference

var items = from n in table where
            n.Name.ToUpper().Contains(searchString.ToUpper().Trim())
            select n;
WriteToLog( items.ToString() );

最后一行输出生成的SQL。下面是我得到的：

The last line outputs the generated SQL. Here's what I get:

SELECT 
    [Extent1].[Name] AS [Name],
    // all the other columns follow
FROM (SELECT 
  [TableName].[Name] AS [Name],
  // all the other columns follow
FROM [dbo].[TableName] AS [TableName]) AS [Extent1]
WHERE ( CAST(CHARINDEX(LTRIM(RTRIM(UPPER(@p__linq__0))), UPPER([Extent1].[Name])) AS int)) > 0

您看，还有 SELECT - 从 - SELECT 尽管这完全是多余的 - 一个选择将只是不够。使用EF的code运行时间超过半分钟的时间了上查询，虽然该表是相当小的。

You see, there's SELECT-from-SELECT although it's completely redundant - one SELECT would be just enough. The code using EF runs longer than half a minute and time out on that query although the table is rather small.

这是为什么过度设计生成的SQL查询以及如何使EF生成更好的查询？

Why is this overengineered SQL query generated and how do I make EF generate a better query?

推荐答案

它通过将一个前pression树生成结果的SQL。它的出现过度设计（例如，使用子查询），作为它的完成了变换方式的副作用。 变换的细节是专有的和复杂的，并且将结果不应该是人类可读的。

It generates the resulting SQL by transforming an expression tree. It appears overengineered (for example, using a subquery) as a side-effect of the way it's done that transformation. The details of the transformation are proprietary and complex, and the results are not supposed to be human-readable.

现在的问题是不完全清楚 - 你正在试图解决一个问题，我认为可能不会是一个问题。尝试比较生成的查询和你自己 - 我猜查询优化器会做出这样一个简单的优化的短期工作。

The question is not entirely clear - and you are trying to solve a problem which I believe may not be a problem. Try comparing the generated query and your own - I would guess the query optimiser will make short work of such an easy optimisation.

我的猜测（这可能是最好的这样回答的，你可以在这里得到，除非LINQ到实体MS开发走来），是他们正在做的正是：生成最有效的查询，但离开头hurtingly-优化查询，他们已经对位困难的工作已经把数百个工日或千成：在SQL Server查询优化器

My guess (and that's probably the best kind of answer you can get here unless a LINQ to Entities MS dev comes along) is that they're doing exactly that: generating the most effective query, but leaving the head-hurtingly-difficult job of optimising the query to the bit they've already put hundreds or thousands of man-days into: the query optimiser in SQL Server.

这篇关于为什么实体框架生成缓慢过度设计的SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！