SQL 与 LINQ 性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13155738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL vs LINQ performance
提问by Teejay
We currently have a self-made entity framework that relies on a DB-indipendent ORM.
我们目前有一个自制的实体框架，它依赖于一个独立于 DB 的 ORM。
I have to build a software that batch-loads metadata in the DB for about 150 excel template (with info on cell position, cell type, formatting and more).
我必须构建一个软件，在数据库中批量加载大约 150 个 excel 模板的元数据（包含有关单元格位置、单元格类型、格式等的信息）。
I can operate
我可以操作
via SQL batch (faster but less interactive)
via building objects in memory, processing them with LINQ queries for various integrity checks, and then committing modifications to the DB
通过 SQL 批处理（更快但交互性更低）
通过在内存中构建对象，使用 LINQ 查询处理它们以进行各种完整性检查，然后将修改提交到数据库
I know that SQL is absolutely faster, but I would know... how much is it faster?
我知道 SQL 绝对更快，但我知道……它快了多少？
In detail, how much is a SQL query faster then a LINQ query (assuming that all needed data has been already loaded in memory by ORM)?
详细地说，SQL 查询比 LINQ 查询快多少（假设所有需要的数据已经被 ORM 加载到内存中）？
采纳答案by Spence
TBH in most cases linq or SQL aren't exactly the issue. Your performance will be related to how much data you are inserting, the amount of data currently in your table and the indexes you are maintaining.
TBH 在大多数情况下 linq 或 SQL 并不是问题所在。您的性能将与您插入的数据量、表中当前的数据量以及您维护的索引有关。
Secondly whether you need to do cross checking and/or integrity checks across multiple columns on your data. I have had situations where adding an index and rebuilding a table has taken insert time down from minutes to milliseconds, just due to bad fragmentation and lack of an algorithm.
其次，您是否需要对数据的多个列进行交叉检查和/或完整性检查。我曾遇到过添加索引和重建表将插入时间从几分钟缩短到几毫秒的情况，这仅仅是由于碎片化和缺乏算法造成的。
Linq is an effective way to generate SQL for insertion and modification logic. However you will always end up with the pattern:
Linq 是一种为插入和修改逻辑生成 SQL 的有效方法。然而，你总是会得到这样的模式：
- Fetch data from database
- Modify data using Linq
- Submit changes to database.
- 从数据库中获取数据
- 使用 Linq 修改数据
- 提交对数据库的更改。
If you have any logic you can exploit in your insertions, you may be able to use set logic to do updates in SQL. E.g. Update Customers Set KeyCustomer = 1 where Sales > 1000000. The SQL Server will process a command like this 1000s of times faster than you could ever do it with your ORM. However as @gbn has already correctly pointed out, unless you have a team full of strong SQL coders, maintenance will often trump any perf gain in the short term.
如果您有任何可以在插入中利用的逻辑，则可以使用设置逻辑在 SQL 中进行更新。例如，更新客户设置 KeyCustomer = 1，其中 Sales > 1000000。SQL Server 处理这样的命令的速度比使用 ORM 快 1000 倍。但是，正如@gbn 已经正确指出的那样，除非您拥有一支由强大的 SQL 编码人员组成的团队，否则维护通常会在短期内胜过任何性能提升。
If you have to insert a significant number of records, then you should really be looking at batch loading and/or ETL via SSIS. These APIs will use smarter algorithms and perform any constraint checks in batches rather than per insert which will give you excellent performance increases. But managing an SSIS package is far more work than clicking a button in an app. These are all design decisions you will need to consider when you architect your application.
如果您必须插入大量记录，那么您真的应该通过 SSIS 查看批量加载和/或 ETL。这些 API 将使用更智能的算法并批量而不是按插入执行任何约束检查，这将为您提供出色的性能提升。但是管理 SSIS 包比单击应用程序中的按钮要复杂得多。这些都是您在构建应用程序时需要考虑的所有设计决策。
回答by Piotr Sobiegraj
Here you have some performance comparations between various ORMs and SqlDataReader: http://code.google.com/p/dapper-dot-net/(Performance section). It's worth to mention that compiling LINQ queries may significantly improve performance: http://www.codeproject.com/Articles/38174/How-to-improve-your-LINQ-query-performance-by-5-X
在这里，您可以对各种 ORM 和以下内容进行一些性能比较SqlDataReader：http: //code.google.com/p/dapper-dot-net/（性能部分）。值得一提的是，编译 LINQ 查询可能会显着提高性能：http: //www.codeproject.com/Articles/38174/How-to-improve-your-LINQ-query-performance-by-5-X
