0

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();
     }
}
Thomas
  • 2,886
  • 3
  • 34
  • 78
  • 2
    If the data doesn't change often, you can cache and retrieve it from your cache. – mason Jan 08 '15 at 20:33
  • the program I'm using the above is consisting of a list of the database entries and an editor to change them (so sadly the program purpose itself is in the end to change exactly taht data)a – Thomas Jan 08 '15 at 20:37
  • Well, there doesn't appear to be many ways you can optimize this then, apart from making sure the web server and database server can talk to each other faster. You can pull all the data at the same time by using [async programming](http://msdn.microsoft.com/en-us/magazine/dn802603.aspx), which is well designed for making multiple DB calls at the same time. – mason Jan 08 '15 at 20:39
  • Ooop. Tnx for mentioning web forgot to mention via tags its winforms. I'm wondering though if tehre really can't be something more done. As such a performance I mean big programs who are programmed with Vs also probably use entity framework and with that performance microsoft would have gone bankrupt from the court cases alone if there wasn't a way to get so "few" rows of data in a time that the user doesn't have to wait much – Thomas Jan 08 '15 at 20:43
  • My suspicion is that the bottleneck is in the communication between your DB server and wherever your Win Forms program is running, not with EF itself. Try it out- try writing the ADO code using SqlCommand and the same queries and see if it makes a difference. My guess is it won't make a difference. So rather than waiting on each DB call to execute synchronously, use some async code to do it all at the same time-the performance increase can be substantial. I've seen 5 synchronous calls that used to take one second each turn into a 1 second wait. 5x faster is pretty impressive. – mason Jan 08 '15 at 20:47
  • And what about the length of the single commands? (even if I do the others asynchronously it would still be 1-3 seconds that the user waits for the edit form to pop up as the single quesries take taht time) – Thomas Jan 08 '15 at 20:50
  • What about them? With async programming, you can often come close to waiting only as long as the longest command takes to execute. That's much better than waiting for each database call to happen one at a time. – mason Jan 08 '15 at 20:52
  • I know but what irks me still is that even 1 single command takes up to 2-3 seconds. and as a user I expect a form to pop up almost immediately ESPECIALLY if its not millios of data rows taht are going to be displaayed / edited – Thomas Jan 08 '15 at 20:53
  • Reality doesn't care whether it irks you or not. If you've determined that it takes X amount of time to do something, then that's how long it takes. Like I said, try async programming so you're doing it all at the same time. Find out where your bottleneck is. SO isn't the place to come to vent your frustration about the speed of your systems. – mason Jan 08 '15 at 20:56
  • @mason I'm not venting my frustation, I'm just having the strong guess that there is another way than what I'm doing to get data and fast at that else entity framework would have been dumped into the dumpster long ago by microsoft (if I programmed a program doing something similar in php I would have gotten the resulting objects in way under 1 second so its quite strange that the above method takes that long by itself). – Thomas Jan 08 '15 at 21:00
  • 1
    Like I said, find your bottleneck. You can't do optimization until you find out where you're slow. Try executing the SQL commands directly with ADO and see what happens. – mason Jan 08 '15 at 21:07
  • ah tnx (deleted my comment now and reposted to his answer). Replied to his comment originally (seems like while I answered he made it into an answer and deleted the comment). – Thomas Jan 09 '15 at 06:16

1 Answers1

0

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.

quetzalcoatl
  • 32,194
  • 8
  • 68
  • 107
  • he number of columns should range from 2 to 30 max. (even with 2 its slower than I would expect). With filters (for example on id) it is faster than without. What I also saw is that about 1/4th of the amount of time each command takes is going into ToList (if a statement takes about 2 seconds, 0.5 seconds are gained if I don't use ToList) – Thomas Jan 09 '15 at 06:15