How to improve AsEnumerable performance in EF

Question

I work on vs2012 ef.

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.

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

score 5 · Accepted Answer · answered Feb 10 '14 at 16:12

5

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.

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).

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.

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.

answered Feb 10 '14 at 16:12

Luaan

62,244
7
97
116

Luaan thanks for reply,I know what is AsEnumerable() activities.Plz read my paragraph carefully ,want to know how to improve performance. – shamim Feb 10 '14 at 16:16
1

@shamim Well, as I said - don't use AsEnumerable. If you can't limit the data you need from those tables considerably (anything more than say 100 rows is probably a bad idea), you simply can't use AsEnumerable there and expect it to be fast. In fact, you're actually downloading the tables to your application, and then sending the IDs back to do further processing on the SQL server. That's a lot of network bandwidth and a lot of work that the SQL server can do much better than your application. – Luaan Feb 10 '14 at 16:18
@shamim I'm not saying you should write custom SQL. The EF provider translates the LINQ query to SQL which is then executed on the SQL server - but your `AsEnumerable` call prevents it from doing exactly that - it separates your query into about three different queries, two of which mean your application downloads complete tables from the DB, and the third then uses this data on the SQL server (ie. requirign you to send the data back to the SQL server again). – Luaan Feb 10 '14 at 16:20
Luaan thanks for your reply. i work on ef,need to write many same type of linq syntax several times,when i avoid AsEnumerable() my syntax show me error.is there any smart way to get better performance or overcome for this slowness behavior of ef. – shamim Feb 10 '14 at 16:28
@shamim The performance problem isn't EF's - you're literally telling EF not to process the data on the server. You have to figure out how to make the query run on the server; however, that's a completely separate question :) At first glance, I'd first join the tables and then group by the results, rather than joining a grouped result. – Luaan Feb 10 '14 at 16:36

How to improve AsEnumerable performance in EF

1 Answers1

Linked