如何从数据库获取数据到objectlists快（与实体框架） [英] How to get data from the database into objectlists fast (with entity framework)

问题描述

我使用实体框架对象LINQ语句从数据库中获取数据到对象的列表。我的问题是性能abmyssal说它好看。对于我处理需要多秒排几百或几千。

我把在下文进一步，但首先一个代码示例我想解释为什么我会中号把我得到的数据为对象的名单，也我以后来到我的问题：

我把数据放入到对象的列表方便地访问数据并同时填写GridView的，并填写组合框，并能用于计算和数据收集属于对象中的其他方法。

正如我敢肯定这用例不是那么很少和我得到的表现真是abmyssal我认为，无论是我使用EF错，错在做LINQ陈述或者我的设计理念的一部分，是完全错误的存在。所以我的问题是我能做些什么来提高性能（或者我需要完全把我的设计理念窗外并做到这一点完全不同......如果又如何）？

代码示例：

对象存储在数据层，也有内部的数据收集方法的对象类（如果在这里出现错误让我知道因为我从内存中输入此）：

 公共类MyObject的
 {
公共标识; 
公开名称; 
 
公共静态列表<&MyObject的GT;使用的GetData（）
 {
（myentities实体=新myentities（））
 {
名单，LT;为MyObject> resultList =（从E在entity.mytable选择新的MyObject的{ID = e.id，名称= e.name}）了ToList（）。 
} 
} 
} 
  

当我打电话myobject.GetData （）为例，以填补3组合框几次的问题是，几十个条目它几乎可以把半秒各。如果我有一对夫妇一千1秒以上的条目。逛了一下尝试之后，我看到了，即使是使用（从而创建新的连接）并没有真正做一个dffierence和表现确实从我如何使用/获取对象列表（LINQ应该生成SQL茎是一样快，我会通常期望当我做这在SQL Server maanagement工作室）。

更新
我做了一些测试，什么也有点奇怪：
当我使用，而不是一个对象时，它仍然是缓慢，本身了ToList需要字符串太长。如果我例如拿回68项需要1.7秒。这个0.5是了ToList方法（这种现象是清楚的数据只准备并聚集在了ToList被调用，但1.7秒在之前的时间太长了，只有数据行的几K）。

 公共静态无效GetNameData（）使用（myentities实体=新myentities（））
 {
 
 {
 （从E在entity.mytable选择e.name）.Distinct（）了ToList（）。 
} 
} 
  

解决方案

的代码，您提供的，以几十名和1千应该是快如闪电的数据大小。当然，除非你正在返回亿列的查询，或者除非你做你的ORM设置一些很奇怪的。

首先，检查你的数据库服务器。它可以在低优先级运行，它可能有完全索引搞砸了，才可能有缓慢的网络连接，或（...）。

顺便说一句。你总是取整表，或者你只是删除你的代码示例过滤器？如果它工作得更快，而不过滤器，检查数据库索引。否则，请检查网络。

另外，尽量在本地建立数据库。如果它太大，修剪下来，它只是测试。然后重试，并检查需要多长时间。它应该是快速。如果不是 - 那么你就可以真正开始假设你有你的EF配置，缓存，数据处理。第在你的应用打破

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();
     }
}

解决方案

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.

这篇关于如何从数据库获取数据到objectlists快（与实体框架）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！