LINQ to Entities Any()和Contains()速度慢，列表小 [英] LINQ to Entities Any() and Contains() slow with small list

问题描述

我正在使用EF 6从数据库中获取产品.产品类别被映射为产品上的导航属性，并且数据来自ProductCategory数据透视表.类别的工作方式就像一棵树(即每个类别都可以有子类别)，但是只有最特定的产品-子类别关系存储在数据透视表中.例如，假设存在这样的类别路径:

I'm using EF 6 to get products from a database. The product categories are mapped as a navigation property on products, and the data is from a ProductCategory pivot table. The categories work like a tree (ie. every category can have sub-categories), but only the most specific product-subcategory relationship is stored in the pivot table. For example, suppose there is category path like this:

电子>音频>放大器>集成放大器.

Electronics > Audio > Amplifiers > Integrated Amps.

作为集成放大器的产品在数据透视表中都有一条记录，其中包含其产品ID和集成安培类别ID.

A product that is an integrated amplifier has a record in the pivot table with its product ID and the Integrated Amps category ID.

我需要按类别进行过滤，但是即使按父类别进行过滤(例如，按集成放大器应显示在放大器列表中.因此，我首先列出相关类别ID. (这涉及到对类别表的单独查询，但不会花很长时间.)如果类别过滤器是放大器，则列表是放大器的ID和集成放大器的ID.

I need to filter by category, but the product should show up even if filtering by a parent category, e.g. an integrated amplifier should show up in a list of Amplifiers. So first I make a List of the relevant category IDs. (This involves a separate query to the categories table, but it doesn't take long.) If the category filter is Amplifiers, the list is the ID of Amplifiers and the ID of Integrated Amps.

问题是，当我包含过滤器时，产品查询的时间要长10-20倍:

The problem is that the products query takes 10-20 times longer when I include the filter:

List<int> currentCategoryIdAndChildren = BuildCategoryIdList(currentCategoryId);

using (var db = new myContext())
{
    var products = db.Products
        .Select(p => new Product_PL
        {
            id = p.ID,
            name = p.Name,
            description = p.Description,
            categories = p.Categories
                        .Select(c => new Category_PL
                        {
                            categoryid = c.ID,
                        }),
        });

    // Filter by category
    products = products.Where(pl => pl.categories.Any(c => currentCategoryIdAndChildren.Contains(c.categoryid)));

    // Other filters, sorting, and paging here

    rptProducts.DataSource = products.ToList(); // Database call is made here
    rptProducts.DataBind();
}

我希望Any()和Contains()的组合会因大量记录而迅速变慢，但是我正在处理产品中的22个项目，pl.categories中的1-3个项目和1-5个项目currentCategoryIdAndChildren中的项目.令我惊讶的是，由于记录如此之少，它的速度降低了一个数量级.以这种速度，我最好不要在客户端进行过滤，即使这意味着要带回许多不必要的记录.

I would expect that combination of Any() and Contains() to slow down quickly with large numbers of records, but I'm working with 22 items in products, 1-3 items in pl.categories, and 1-5 items in currentCategoryIdAndChildren. I'm surprised that with so few records it's slower by an order of magnitude. At this rate I'm better off filtering it client side, even though it means bringing back a lot of unnecessary records.

有什么我想念的吗?还有另一种方法吗?

Is there something I'm missing? Is there another approach?

更新:Express Profiler报告数据库查询本身仅花费3毫秒，因此我猜测性能与Entity Framework的工作方式有关.当然，这是第一次运行LINQ时最慢的速度(我知道它需要编译查询)，但是在后续调用中仍然相对较慢.

UPDATE: Express Profiler reports that the database query itself only takes 3ms, so I am guessing the performance has something to do with how Entity Framework works. Certainly it is slowest the very first time the LINQ is run (I know it needs to compile the query), but it's still relatively slow on subsequent calls.

推荐答案

我尝试了许多不同的方法，终于找到了解决方案.

I tried many different things and finally found a solution.

我相信主要的减速是在EF将Contains()转换为SQL查询时发生的.但是，最值得注意的是，它似乎没有缓存查询.据我所知，这是因为类别ID列表(currentCategoryIdAndChildren)是在EF外部生成的，因此它每次都将有所不同.

I believe the main slowdown happened when EF was translating the Contains() into a SQL query. The most noticeable thing, however, was that it did not appear to cache the query. From what I can gather, this is because the list of category IDs (currentCategoryIdAndChildren) was generated outside of EF, so it assumed it would be different every time.

通过使用LINQKit中的PredicateBuilder，我能够加快处理速度.这使我可以更明确地创建逻辑:

I was able to speed things up by using the PredicateBuilder in LINQKit. This allowed me to create the logic more explicitly:

var IsInCategory = PredicateBuilder.False<Product_PL>();

foreach (int categoryID in currentCategoryIdAndChildren)
{ IsInCategory = IsInCategory.Or(pl => pl.categories.Any(c => categoryID == c.categoryid)); }

products = products.Where(IsInCategory);

这使我的初始查询获得了更好的性能，而随后的查询获得了更好的性能.

This got me a bit better performance with my initial query, and MUCH better performance with subsequent queries.

这篇关于LINQ to Entities Any()和Contains()速度慢，列表小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！