问题描述
我们目前有一个依赖于DB辅助ORM的自制实体框架.
We currently have a self-made entity framework that relies on a DB-indipendent ORM.
我必须构建一个软件，以将数据库中的元数据批量加载到大约150个excel模板中(以及有关单元格位置，单元格类型，格式等的信息).
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).
我可以操作
-
通过SQL批处理(更快，但交互性更小)
via SQL batch (faster but less interactive)
通过在内存中构建对象，使用LINQ查询处理它们以进行各种完整性检查，然后将修改提交给数据库
via building objects in memory, processing them with LINQ queries for various integrity checks, and then committing modifications to the DB
我知道SQL绝对快，但是我会知道... 它快多少?
I know that SQL is absolutely faster, but I would know... how much is it faster?
详细地说，SQL查询比LINQ查询要快多少?(假设ORM已将所有需要的数据加载到内存中了)?
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) ?
推荐答案
在大多数情况下，linq或SQL并不是TBH的问题.性能将与您要插入的数据量，表中当前的数据量以及要维护的索引有关.
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.
第二，是否需要对数据的多个列进行交叉检查和/或完整性检查.我曾经遇到过这样的情况，即由于碎片不正确和缺乏算法，添加索引和重建表的插入时间从几分钟缩短到了几毫秒.
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是生成用于插入和修改逻辑的SQL的有效方法.但是，您总是会遇到以下模式:
Linq is an effective way to generate SQL for insertion and modification logic. However you will always end up with the pattern:
- 从数据库中获取数据
- 使用Linq修改数据
- 将更改提交到数据库.
如果有任何可以在插入中利用的逻辑，则可以使用set逻辑在SQL中进行更新.例如.更新客户设置KeyCustomer = 1，其中Sales>1000000.SQLServer处理这样的命令的速度比您使用ORM所能完成的速度快1000倍.但是，正如@gbn已经正确指出的那样，除非您拥有一支由强大的SQL编码人员组成的团队，否则维护通常会在短期内胜过任何性能提升.
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.
如果您必须插入大量记录，那么您实际上应该通过SSIS查看批处理加载和/或ETL.这些API将使用更智能的算法，并分批执行任何约束检查，而不是每个插入执行一次，这将为您带来出色的性能提升.但是，与单击应用程序中的按钮相比，管理SSIS包要花更多的功夫.这些都是在设计应用程序时需要考虑的设计决策.
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.
这篇关于SQL vs LINQ性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！