LINQ to SQL / LINQ to Collections 性能
有两个选项可用于处理使用 SQL 填充的 LINQ to Collections（我使用的是 Oracle 提供程序，因此没有 ORM 就没有 LINQ）。
执行一个大型 SQL 查询，将结果转储到某种集合中，并对集合执行 LINQ 查询，这样您就可以对数据库进行一次大的调用，但之后不会有太大的减速。
执行小型 SQL 查询并将结果转储到许多较小的集合中，并对这些集合执行 LINQ 查询，这样您对数据库的消耗较小，但整个应用程序的速度减慢更加一致。
有人对此有什么想法吗？
There's two options for dealing with LINQ to Collections that are populated with SQL (I'm using an Oracle provider, so no LINQ without an ORM).
Do one big SQL query, dump the results into some sort of collection and do LINQ queries on the collection, so you have one big draw on the database, but not much slowdown after that.
Do small SQL queries and dump the results into many smaller collections and do LINQ queries on those, so you have smaller draws on the database, but more consistent slowdowns throughout the application.
Anyone have any thoughts on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这两种方法有一个很大的区别：如果每次需要数据时都查询数据库，就会得到最新的数据，而如果一次性读入所有数据并重复使用，则不会看到最新的数据。更改，直到您再次阅读所有内容。两种系统都有优点和缺点，但您需要意识到这种差异。
关于性能差异 - 不要假设本地数据上的 LINQ to Objects 总是比数据库查询更快。数据库针对不同类型的查询进行了令人难以置信的优化，并且可以利用索引。 LINQ to Object 查询通常只是迭代整个数据集。因此，即使您在本地拥有数据，除非您自己努力对数据建立索引，但某些查询实际上可能比您让数据库来完成工作要慢。
即使对于无法使用索引的查询，数据库仍然可以击败简单的 LINQ to Objects 方法。数据库有一些非常先进的算法，这些算法未在 LINQ to Objects 中实现。例如，一个常见的查询是使用过滤器获取按某些条件排序的前 100 个项目。即使没有足够大的结果集的可用索引，数据库的性能仍可能优于 LINQ to Objects，因为
OrderBy(x => x.Foo).Take(100)将首先执行 O(n log n) 排序，然后取出前一百个元素并丢弃其余的。 SQL Server 团队知道这种类型的查询很常见，因此他们添加了一个特殊的优化调用 TOP N SORT 可以在 O(n) 时间内执行此操作。我想Oracle也有类似的优化。我写了另一个答案，详细介绍了这一点点，包括 LINQ to SQL 与 LINQ to Objects 查询的一些性能测量。There is one big difference between these two methods: if you query the database each time you need the data you will get the most recent data, whereas if you read in all the data in one go and reuse it then you will not see the newest changes until you read everything in again. Both systems have advantages and disadvantages, but you need to be aware of this difference.
Regarding performance differences - don't assume that LINQ to Objects on local data will always be faster than a database query. Databases are incredibly well optimized for different types of queries and can take advantage of indexes. LINQ to Object queries generally just iterate over the entire data set. So even if you have the data locally unless you make an effort to index the data yourself some queries might actually be slower than if you get the database to do the work.
Even for queries where indexes can't be used databases can still beat a naive LINQ to Objects approach. Database have some very advanced algorithms that aren't implemented in LINQ to Objects. For example, a common query is to fetch the top 100 items sorted by some criteria with a filter. Even without a usable index for a sufficient large result set the database might still outperform LINQ to Objects because
OrderBy(x => x.Foo).Take(100)will first perform an O(n log n) sort and then afterwards take the first hundred elements and discard the rest. The SQL Server team knows that this type of query is common and so they added a special optimization call TOP N SORT which can perform this operation in O(n) time. I imagine that Oracle has a similar optimization. I have written another answer that goes into more details on this point, including some performance measurements of a LINQ to SQL versus a LINQ to Objects query.