How to get data from the database into objectlists fast (with entity framework)

c# entity-framework-6 linq visual-studio-2013 winforms

Question

I'm using linq statements on entity framework objects to get data from the database into lists of objects. My problem is that the performance is abmyssal to say it nice. For a couple hundred or thousand of rows that I process it takes multiple seconds.

I'm putting in a code example further below but first I want to explain WHY I'm putting the data I get into lists of objects and also I'm coming to my question after that:

I'm putting the data into lists of objects to easily access the data and also fill gridviews, and fill comboboxes and be able to use additional methods for calculations and data gathering that are inside the objects.

As I'm pretty sure this usecase is not so seldom and the performance I get is really abmyssal I take it that either I'm using EF wrongly, doing the linq statements wrongly or part of my design idea is completely wrong there. So my question is what can I do to improve the performance (or do I need to completely throw my design idea out of the window and do it completely different...and if so how)?

Code example:

The objects are stored in object classes in the data layer which also have the data gathering methods inside (if there is an error in here let me know as I'm typing this from memory):

public class myobject
{
    public id;
    public name;

    public static List<myobject> GetData()
    {
         using (myentities entity = new myentities())
         {
              List<myobject> resultList = (from e in entity.mytable select new myobject{ id=e.id, name=e.name}).ToList();
         }
    }
}

When I call myobject.GetData() a few times for example to fill 3 comboboxes the problem is that for a few dozen entries it can take almost half a second each. If I have a couple thousand entries its over 1 second. After trying around a bit I saw that even the using (thus creating new connections) does not really make a dffierence and the performance really stems from how I use/get the object list (the sql that linq should generate is as fast as I would normally expect when I do it in sql server maanagement studio).

Update I made a few tests and what is also a bit strange: When I use String instead of an object it is still slow and the ToList itself takes way too long. If I for example get back 68 entries it takes 1.7 seconds. 0.5 of this is the ToList method (This phenomenon is clear as the data is only prepared and gathered when tolist is called BUT the 1.7 seconds before are way too long with only a few k of datarows).

public static void GetNameData()
{
     using (myentities entity = new myentities())
     {
          (from e in entity.mytable select e.name).Distinct().ToList();
     }
}
1
0
1/12/2015 10:36:25 AM

Popular Answer

The code you provided, with the datasize of "few dozen" and "1 thousand" should be lightning fast. Of course unless you are returning a query of million columns, or unless you did something really strange to your ORM setup.

First, check your database server. It may be running on low priority, it may have indexes totally screwed up, it may have slow network connection, or (...).

Btw. are you always fetching whole table, or you just removed filters in your code example? If it works much faster without filters, check db indices. Otherwise, check network.

Also, try setting up the database locally. If it's too large, trim it down, it's just test. Then retry and check how long it takes. It should be fast. If it's not - then you can really start assuming you got your EF config, cache, data procesing broken in your app.

0
1/8/2015 9:21:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow