如何提高EF中的AsEnumerable性能 [英] How to improve AsEnumerable performance in EF

问题描述

我在vs2012 ef上工作。

I work on vs2012 ef.

我的edmx中有1对多的映射表结构。

I have 1 to many mapping table structure in my edmx.

var query = (
                    from bm in this.Context.BilBillMasters.AsEnumerable ()

                        join g in
                        (
                            from c in this.Context.BilBillDetails.AsEnumerable ()
                            group c by new { c.BillID }
                        )
                    on bm.BillID equals (g == null ? 0 : g.Key.BillID) into bDG
                    from billDetailGroup in bDG.DefaultIfEmpty()


                    where bm.IsDeleted == false
                    && (companyID == 0 || bm.CompanyID == companyID)
                    && (userID == 0 || bm.CustomerID == userID)
                    select new
                    {
                        bm.BillID,
                        BillNo = bm.CustomCode,
                        bm.BillDate,
                        BillMonth = bm.MonthFrom,
                        TransactionTypeID = bm.TransactionTypeID ?? 0,
                        CustomerID = bm.CustomerID,
                        Total = billDetailGroup.Sum(p => p.Amount),//group result


                        bm.ReferenceID,
                        bm.ReferenceTypeID

                    }
                    );






This method is taking close 30 seconds to return back the result in the first run.

不知道出什么问题了。
我尝试获取结果列表并尝试了也很慢的elementAt（0）。

Not sure what is wrong. I tried getting List of results and tried elementAt(0) that is also slow.

推荐答案

使用 AsEnumerable ，您的查询将不再是可查询的。这意味着您要做的是下载整个 BilBillMasters 和 BilBillDetails 表，然后执行一些操作处理应用程序中的内容，而不是SQL Server上的内容。

As soon as you use AsEnumerable, your query stops being a "queryable". That means that what you're doing is that you're downloading the whole BilBillMasters and BilBillDetails tables and then doing some processing on those in your application, rather than on the SQL server. This is bound to be slow.

显而易见的解决方案是显而易见的-不要使用 AsEnumerable -它基本上可以移动从SQL服务器（具有所有数据和索引等）进行处理到应用程序服务器（既没有数据又必须从DB服务器获取数据；所有数据）。

The obvious solution is obvious - don't use AsEnumerable - it basically moves processing from the SQL server (which has all the data and indexes etc.) to your application server (which has neither and has to get the data from the DB server; all of the data).

至少，您想限制下载的数据量，即。例如，在使用 AsEnumerable CompanyID 和 CustomerID 过滤表>。但是，总的来说，我认为没有理由无法在SQL Server上完全执行查询-由于许多原因，这通常是首选的解决方案。

At the very least, you want to limit the amount of data downloaded as much as possible, ie. for example filter the tables by CompanyID and CustomerID before using AsEnumerable. However, overall, I see no reason why the query couldn't be executed completely on the SQL server - this is usually the preferred solution for many reasons.

总体而言，它听起来好像您正在使用 AsEnumerable 来解决另一个问题，但这绝对是一个糟糕的解决方案-至少在使用<$ c之前不对数据进行进一步过滤$ c> AsEnumerable 。

Overall, it sounds as if you're using the AsEnumerable as a fix to another problem, but it's almost definitely a bad solution - at least without further filtering of the data before using AsEnumerable.

这篇关于如何提高EF中的AsEnumerable性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！