实体框架查询太慢

Question

我是一个新手，对于实体框架(entity framework)很感兴趣，但在速度方面遇到了一些问题。我认为我可能没有正确使用懒加载(lazy loading)，但是很难理解它的工作原理。我已经将我的数据模型层和业务实体层分开，并使用一个函数从我的数据模型中创建业务实体。在这个函数中，我遍历不同的嵌套实体来创建它们相应的模型。好了，够啰嗦的了，下面是一些代码：

IM_ITEM.cs (产品数据模型)

public partial class IM_ITEM
{
    public IM_ITEM()
    {
        this.IM_INV = new HashSet<IM_INV>();
        this.IM_BARCOD = new HashSet<IM_BARCOD>();
        this.IM_GRID_DIM_1 = new HashSet<IM_GRID_DIM_1>();
        this.IM_GRID_DIM_2 = new HashSet<IM_GRID_DIM_2>();
        this.IM_GRID_DIM_3 = new HashSet<IM_GRID_DIM_3>();
        this.IM_PRC = new HashSet<IM_PRC>();
    }

    public string ITEM_NO { get; set; }
    public string DESCR { get; set; }
    // many more properties...

    public virtual ICollection<IM_INV> IM_INV { get; set; }
    public virtual ICollection<IM_BARCOD> IM_BARCOD { get; set; }
    public virtual ICollection<IM_GRID_DIM_1> IM_GRID_DIM_1 { get; set; }
    public virtual ICollection<IM_GRID_DIM_2> IM_GRID_DIM_2 { get; set; }
    public virtual ICollection<IM_GRID_DIM_3> IM_GRID_DIM_3 { get; set; }
    public virtual ICollection<IM_PRC> IM_PRC { get; set; }
}

企业实体创建方法:

public static ProductEntity FromEfObject(IM_ITEM obj) {
    var product = new ProductEntity {
        ItemNumber = obj.ITEM_NO,
        StyleNumber = obj.VEND_ITEM_NO,
        Title = obj.DESCR_UPR,
        LongName = obj.ADDL_DESCR_1,
        ShortDescription = obj.DESCR,
        VendorCode = obj.ITEM_VEND_NO,
        Quarter = obj.ATTR_COD_2,
        Color = obj.PROF_ALPHA_2,
        Markdown = obj.PRC_1,
        Price = obj.REG_PRC ?? 0,
        Status = obj.STAT,
        DepartmentCode = obj.ATTR_COD_1,
        DepartmentDigit = obj.ATTR_COD_1.Substring(0, 1),
        MixAndMatch = obj.MIX_MATCH_COD,
        Inventory = new Inventory(obj.IM_INV),
        Sizes = new List<ProductSize>(),
        Widths = new List<ProductSize>(),
        Lengths = new List<ProductSize>(),
        Barcodes = new Dictionary<string, string>()
    };


    if (obj.IM_PRC.Any()) {
        var price = obj.IM_PRC.First();
        product.DnsPrice2 = price.PRC_2.GetValueOrDefault();
        product.DnsPrice3 = price.PRC_3.GetValueOrDefault();
    }

    foreach (var barcode in obj.IM_BARCOD) {
        product.Barcodes.Add(barcode.DIM_1_UPR, barcode.BARCOD);
    }

    foreach (var size in obj.IM_GRID_DIM_1) {
        product.Sizes.Add(ProductSize.FromEfObject(size));
    }

    foreach (var width in obj.IM_GRID_DIM_2) {
        product.Widths.Add(ProductSize.FromEfObject(width));
    }

    foreach (var length in obj.IM_GRID_DIM_3) {
        product.Lengths.Add(ProductSize.FromEfObject(length));
    }

    if (!product.Sizes.Any()) {
        product.Sizes.Add(new ProductSize());
    }

    if (!product.Widths.Any()) {
        product.Widths.Add(new ProductSize());
    }

    if (!product.Lengths.Any()) {
        product.Lengths.Add(new ProductSize());
    }

    return product;
}

我用的方法来获取模型：

public ProductEntity GetProductById(string itemNumber, int storeNumber) {
    var product = _unitOfWork
        .GetProductRepository(storeNumber)
        .GetQueryable()
        .FirstOrDefault(p => p.ITEM_NO == itemNumber);

    return product == null ? null : ProductEntity.FromEfObject(product);
}

GetQueryable方法:

internal DbSet<TEntity> DbSet;
public GenericRepository(TContext context)
{
    Context = context;
    DbSet = context.Set<TEntity>();
}

public virtual IQueryable<TEntity> GetQueryable()
{
    IQueryable<TEntity> query = DbSet;
    return query;
}

稍微提供一些信息..我使用数据库优先建模来创建我的数据模型，我正在测试的数据库没有大量数据。此外，我尝试在我的GetProductById方法中使用.Include()来加载（我认为是急切地），但这甚至使它变得更慢了。

我有什么做错了吗？或者像这样查询使用EF会很慢吗？

编辑：为了防止延迟加载，我更新了我的查询：

    public ProductEntity GetProductById(string itemNumber, int storeNumber) {
        var product = _unitOfWork
            .GetProductRepository(storeNumber)
            .GetQueryable()
            .Include(p => p.IM_INV.Select(i => i.IM_INV_CELL))
            .Include(p => p.IM_BARCOD)
            .Include(p => p.IM_GRID_DIM_1)
            .Include(p => p.IM_GRID_DIM_2)
            .Include(p => p.IM_GRID_DIM_3)
            .Include(p => p.IM_PRC)
            .FirstOrDefault(p => p.ITEM_NO == itemNumber);

        return product == null ? null : ProductEntity.FromEfObject(product);
    }

在追踪过程中，这给我一个很长的、比使用惰性加载更耗时的恶意查询 http://pastebin.com/LT1vTETb

- Troy Cosentino

---

你对 _unitOfWork.GetProductRepository(storeNumber).GetQueryable() 的实现方式是什么？如果这一部分有误，可能会导致 EF 在执行 FirstOrDefault 前将整个产品表查询到内存中，从而使查询变慢。请注意不要改变原意。 - SynerCoder

storeNumber是否会影响加载的DbContext，还是它只是查询的一部分？CQ，你实现的GetQueryable是为通用仓储设计的，但产品仓库应该怎么样呢？ - SynerCoder

那会影响已加载的DbContext。不同的商店使用不同的数据库。我将检查SQL Profiler，看看能否获得更好的图片。谢谢你的提示。我做了一些基本的时间测量来查看什么是慢的，似乎在FromEfObject()方法中分布相当均匀。IM_PRC块需要一秒钟，条形码foreach等需要一秒钟。 - Troy Cosentino

@TroyCosentino 作为一个测试，如果您只映射直接属性而没有外键，您会得到什么样的性能？如果加入以下包含关系呢？然后尝试逐个添加外键映射，以查明哪些是慢的。 - Tim Copenhaver

追踪它，我发现我使用每个对象(IM_PRC、IM_BARCOD等)都有一个查询。看起来很好... 每个查询之后都会有一个持续时间大约为10000的'Audit Logout'，比查询时间要长得多，但从搜索结果来看，我不必担心这个问题。我发现它非常不一致——通常Web服务需要6-10秒，但偶尔会是1.5秒或15秒。没有其他东西在访问数据库，所以我有点困惑... 将继续深入研究。 - Troy Cosentino

Answer 1

你可以优化查询以避免懒加载。在这种情况下，当你从数据库加载一个对象时，你知道你将不得不将几乎整个对象图映射到内存中。无论如何，你以后都需要查找所有这些外键 - 把它们全部作为一个查询处理比让懒加载来完成更快。更多信息请参见此处。

应该看起来像这样：

public ProductEntity GetProductById(string itemNumber, int storeNumber) {
    var product = _unitOfWork
        .GetProductRepository(storeNumber)
        .GetQueryable()
        .Include(p => p.IM_BARCOD)
        .Include(p => p.IM_GRID_DIM_1)
        .Include(p => p.IM_GRID_DIM_2)
        .Include(p => p.IM_GRID_DIM_3)
        .Include(p => p.IM_PRC)
        .FirstOrDefault(p => p.ITEM_NO == itemNumber);

    return product == null ? null : ProductEntity.FromEfObject(product);
}

请注意，如果这些外键有它们自己的外键（即IM_BARCOD有IM_OtherType的集合），并且您还需要将其映射到ProductEntity模型中，则应该包括这些内容。您可以像这样在同一行中完成：

.Include(p => p.IM_BARCOD.Select(b => b.IM_OTHERTYPE))

- Tim Copenhaver

---

在这种情况下，我想在包含之前执行.Where(p => p.ITEM_NO == itemNumber)，对吗？我测试了一下，时间从大约8秒增加到了20秒，所以我回到了没有包含的调试状态。 - Troy Cosentino

不需要在前面加Where。使用EF时，所有这些代码都会被转换为单个SQL查询 - 它实际上并没有在C#中执行。语句的顺序并不重要，因为最终它会转换为相同的SQL查询。 - Tim Copenhaver

好的，这很好知道。我知道它会编译成一个查询，但不知道它是否足够智能来排序。顺便问一下，如果我有几个Where语句，它是否会为我优化顺序？ - Troy Cosentino

拥有多个where语句在语义上与在单个where中使用多个条件和&&是相同的。EF本身可能无法对此进行优化，但您的DBMS的查询优化器很可能会（例如，MS SQL Server会）。 - Tim Copenhaver