我有这个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.