实体，处理大量记录（> 35毫升） [英] Entity, dealing with large number of records (> 35 mlns)

问题描述

我们拥有相当大的相关表格，每个相关记录超过3500万条。我需要创建一些WCF方法，使用一些参数（数据范围，类型代码等）查询数据库，并返回相关的结果集（从10到10,000个记录）。

We have a rather large set of related tables with over 35 million related records each. I need to create a couple of WCF methods that would query the database with some parameters (data ranges, type codes, etc.) and return related results sets (from 10 to 10,000 records).

该公司在EF 4.0上标准化，但对4.X开放。我可能会将争论转移到5.0，但不太可能。

The company is standardized on EF 4.0 but is open to 4.X. I might be able to make argument to migrate to 5.0 but it's less likely.

使用Entity处理如此大量记录的最佳方法是什么？我应该创建一组存储过程并从Entity中调用它们，还是在Entity中有一些我可以做的？

What’s the best approach to deal with such a large number of records using Entity? Should I create a set of stored procs and call them from Entity or there is something I can do within Entity?

我没有任何数据库的控制权，所以我不能拆分表或创建一些物化视图或分区表。

I do not have any control over the databases so I cannot split the tables or create some materialized views or partitioned tables.

非常感谢任何输入/想法/建议。

Any input/idea/suggestion is greatly appreciated.

推荐答案

在我的工作中，我面临着类似的情况。我们有一个数据库有许多表，其中大多数包含大约7千万个记录。我们使用Entity框架来显示数据，但页面似乎显示得非常慢（如90到100秒）。即使在网格上排序也需要时间。我得到了任务，看看它是否可以优化。并且在对其进行分析之后（ANTS profiler），我可以优化它（7秒以下）。

At my work I faced a similar situation. We had a database with many tables and most of them contained around 7- 10 million records each. We used Entity framework to display the data but the page seemed to display very slow (like 90 to 100 seconds). Even the sorting on the grid took time. I was given the task to see if it could be optimized or not. and well after profiling it (ANTS profiler) I was able to optimize it (under 7 secs).

所以答案是是的，实体框架可以处理负载的记录（以百万为单位），但必须小心

1. 了解仅当实际记录为需要。所有的操作只是用来使查询（SQL），所以尝试只获取一块数据，而不是请求大量的记录。修剪抓取大小尽可能


2. 是的，您不应该使用存储过程并将其导入到模型中并为其输入功能。您也可以直接调用它们ExecuteStoreCommand（），ExecuteStoreQuery<（（））。 Sames的功能和视图，但EF有一个非常奇怪的方式调用函数SELECT dbo.blah（@id）。


3. 当它必须深入填充一个实体时，EF执行得更慢层次结构。


4. 有时，当您要求记录，您不需要修改它们时，您应该告诉EF不要观察属性更改（AutoDetectChanges）。这种记录检索速度要快得多。


5. 数据库的索引很好，但是在EF的情况下变得非常重要。您用于检索和排序的列应该被正确索引。


6. 当您的型号很大时，VS2010 / VS2012型号设计师变得真实疯狂。所以将你的模型打破中型模型。有一个限制，不同模型的实体不能共享，即使它们可能指向数据库中的同一个表。


7. 当您必须在不同的实体进行更改时地方，尝试通过传递使用相同的实体，并发送更改只有一次，而不是每个人获取一个新的片段，进行更改和存储（实际性能增益提示）。


8. 当您只需要一列或两列中的信息，尽量不要获取完整的实体。你可以直接执行你的sql或一个迷你实体。您可能需要在您的应用程序中缓存一些常用的数据。


9. 事务缓慢。要小心他们。

1. Understand that call to database made only when the actual records are required. all the operations are just used to make the query (SQL) so try to fetch only a piece of data rather then requesting a large number of records. Trim the fetch size as much as possible
2. Yes, not you should, you must use stored procedures and import them into your model and have function imports for them. You can also call them directly ExecuteStoreCommand(), ExecuteStoreQuery<>(). Sames goes for functions and views but EF has a really odd way of calling functions "SELECT dbo.blah(@id)".
3. EF performs slower when it has to populate an Entity with deep hierarchy. be extremely careful with entities with deep hierarchy .
4. Sometimes when you are requesting records and you are not required to modify them you should tell EF not to watch the property changes (AutoDetectChanges). that way record retrieval is much faster
5. Indexing of database is good but in case of EF it becomes very important. The columns you use for retrieval and sorting should be properly indexed.
6. When you model is large, VS2010/VS2012 Model designer gets real crazy. so break your model into medium sized models. There is a limitation that the Entities from different models cannot be shared even though they may be pointing to the same table in the database.
7. When you have to make changes in the same entity at different places, try to use the same entity by passing it and send the changes only once rather than each one fetching a fresh piece, makes changes and stores it (Real performance gain tip).
8. When you need the info in only one or two columns try not to fetch the full entity. you can either execute your sql directly or have a mini entity something. You may need to cache some frequently used data in your application also.
9. Transactions are slow. be careful with them.

如果你记住这些事情，EF应该提供几乎类似的性能，如简单的ADO.NET，如果不一样。

if you keep these things in mind EF should give almost similar performance as plain ADO.NET if not the same.

这篇关于实体，处理大量记录（> 35毫升）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！