>的EF6 SQL生成。

Trying to upgrade from EF5 to EF6, I encounter significant performance gap searching table by nullable column. Here is a sample:

尝试从EF5升级到EF6，我在可空列中遇到了显著的性能差距搜索表。这是一个示例:

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

When EF6 generates SQL, it adds some logic for null handling:

当EF6生成SQL时，它为null处理添加了一些逻辑:

SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Customers] AS [Extent1]
WHERE (([Extent1].[ManagerId] = @p__linq__0) 
AND ( NOT ([Extent1].[ManagerId] IS NULL OR @p__linq__0 IS NULL))) 
OR (([Extent1].[ManagerId] IS NULL) AND (@p__linq__0 IS NULL))

Notice same linq produced more simple SQL under EF5:

注意同样的linq在EF5下面生成了更简单的SQL:

SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[ManagerId] = @p__linq__0

I can understand the point developers tried to achieve: if you supply null as parameter, the query where managerId = null will not select any lines. I appreciate the care, but 99.9% times search logic is separated: one usecase looks for where ManagerId == null, another searches for specific id where ManagerId == managerId

我可以理解开发人员试图达到的目的:如果您提供null作为参数，那么managerId = null的查询将不会选择任何行。我很感谢您的关心，但是99.9%的搜索逻辑是分开的:一个usecase查找ManagerId == null，另一个搜索特定id，其中ManagerId == ManagerId。

The problem is in big performance impact: MS SQL does not use index on ManagerId and table scan occurs. My project has hundreds of similar searches and on database size about 100GB overall performance after upgrade to EF6 cut by 10 approximately.

这个问题在性能上有很大的影响:MS SQL不使用ManagerId和表扫描的索引。我的项目有数百个类似的搜索，数据库大小约为100GB，在升级到EF6后，大约减少了10个。

The question is does anybody know some kind of configuration or convention to disable this roadblock in EF6 and generate simple sql?

问题是，有没有人知道某种配置或约定在EF6中禁用这个路障并生成简单的sql?

EDIT:

编辑:

I checked a dozen similar selects in my project and found that:

我在我的项目中检查了一打类似的选择，发现:

In some cases SQL SERVER does use the index specified for the field I search. And even in this cases there is slight performance loss: it uses index twice: first time looking for value I specified in parameter, second time looking for null
在某些情况下，SQL SERVER确实使用为我搜索的字段指定的索引。即使在这种情况下，也有轻微的性能损失:它使用索引两次:第一次查找参数中指定的值，第二次查找空值。

EF6 even checks for null when constant is exactly specified as not null, for example:

当常量被精确指定为not null时，EF6甚至检查null，例如:

            from p in db.PtnActivations
        where p.Carrier != "ALLTEL"
        where p.Carrier != "ATT"
        where p.Carrier != "VERIZON"

generates SQL

生成SQL

    WHERE ( NOT (('ALLTEL' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL))) AND ( NOT (('ATT' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL))) AND ( NOT (('VERIZON' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL)))

that did not utilize my index on carrier. EF5 version had

这并没有利用我的指数。EF5版本了

( NOT (('ALLTEL' = [Extent1].[Carrier]))) AND ( NOT (('ATT' = [Extent1].[Carrier]))) AND ( NOT (('VERIZON' = [Extent1].[Carrier]) ))

that utilized it.

利用它。

Notice the condition ('ALLTEL' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL). Second part is always false, but adding this part gives up the index.

注意到条件('ALLTEL' = [Extent1].[载波])和([Extent1])。(载体)NOT NULL)。第二部分总是错误的，但添加这部分就放弃了索引。

My routine import of about 1.7M records (that usually had taken about 30 minutes) is up for 3 hours and progress is about 30%.

我的常规进口约170万张(通常是30分钟左右)，持续3个小时，进度约为30%。

2 个解决方案

#1

Set

集

db.Configuration.UseDatabaseNullSemantics = true;

to get the behavior you had in EF5. This workitem describes what the difference between true and false is and should help you decide whether you are OK with the old behavior or not.

为了得到你在EF5中的行为。这个工作项目描述了真实和错误之间的区别，并且应该帮助你决定你是否对旧行为满意。

2 个解决方案

#1

更多相关文章

随机推荐