C# 在 sql server 精简版上执行插入或更新（upsert）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/728373/
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
Performing Insert OR Update (upsert) on sql server compact edition
提问by RBear
I have c# project that is using sqlserver compact edition and entity framework for data access. I have the need to insert or update a large amount of rows, 5000+ or more to the db, so if the key exists update the record if not insert it. I can not find a way to do this with compact edition and EF with out horrible performance, ie taking 2 mins plus on a core i7 computer. I have tried searching for the record to see if it exists then inserting if not or update if it does, the search is the killer on that. I have tried compiling the search query and that only gave a small improvement. Another thing ive tried is inserting the record in a try catch and if it fails update, but that forces me to savechanges on every record to get the exception as opposed to at the end which is a performance killer. Obviously I can't use stored procedures since it is compact edition. Also I've looked at just executing t-sql directly somehow on the db, but lack of process statements in compact seems to rule that out. I've searched the world over and out of ideas. I really wanted to use compact if I can over express for the deployment benefits and ability to prevent the user from digging around the db. Any suggesitons would be appreciated.
我有使用 sqlserver 精简版和实体框架进行数据访问的 c# 项目。我需要插入或更新大量的行，5000+ 或更多到数据库，所以如果键存在，如果不插入则更新记录。我无法找到一种方法来使用精简版和 EF 执行此操作而没有可怕的性能，即在核心 i7 计算机上花费 2 分钟以上。我已经尝试搜索记录以查看它是否存在，然后插入如果不存在或更新，如果存在，搜索是其中的杀手。我试过编译搜索查询，但只得到了一点点改进。我尝试过的另一件事是在 try catch 中插入记录，如果它更新失败，但这迫使我在每条记录上保存更改以获取异常，而不是在最后，这是性能杀手。显然我不能 t 使用存储过程，因为它是精简版。此外，我还查看了在数据库上以某种方式直接执行 t-sql 的方法，但缺少紧凑的进程语句似乎排除了这一点。我已经在世界各地寻找想法。如果我可以过度表达部署优势和防止用户挖掘数据库的能力，我真的很想使用紧凑型。任何建议将不胜感激。
Thanks
谢谢
采纳答案by Andrea Bertani
Maybe you could obtain the result you seek by using simple queries. Let's say the the table you want to insert into or update is like this
也许您可以通过使用简单的查询来获得您想要的结果。假设您要插入或更新的表是这样的
TABLE original
id integer,
value char(100)
first you could create a temporary table with the new values (you can use a SELECT INTO or other ways to create it)
首先，您可以使用新值创建一个临时表（您可以使用 SELECT INTO 或其他方式来创建它）
TABLE temp
id integer,
value char(100)
now, you need to do two things, update the rows in original and then insert the new values
现在，您需要做两件事，更新原始行，然后插入新值
UPDATE original
SET original.value = temp.value
FROM original, temp
WHERE original.id = temp.id
INSERT INTO original
SELECT * from temp
WHERE temp.id not IN (select o.id from original o)
回答by Greg D
Given your problem statement, I'm going to guess that this software assumes a relatively beefy environment. Have you considered taking the task of determining off of sqlce and doing it on your own? Essentially, grab a sorted list of all the IDs(keys?) from the relevant table and checking every object key against that list before queueing it for insertion?
鉴于您的问题陈述，我猜想这个软件假设了一个相对强大的环境。您是否考虑过将确定 sqlce 的任务交给您自己完成？本质上，从相关表中获取所有 ID（键？）的排序列表，并在将其排队插入之前根据该列表检查每个对象键？
This makes a few assumptions that would be bad news with a typical DB, but that you can probably get away with in sqlce. E.g., it assumes that rows won't be inserted or significantly modified by a different user while you're performing this insert.
这做出了一些假设，这对于典型的数据库来说是个坏消息，但您可能可以在 sqlce 中逃脱。例如，它假定在您执行此插入时不会被其他用户插入或显着修改行。
If the list of keys is too long to reasonably hold in memory for such a check, I'm afraid I'd say that sqlce just might not be the right tool for the job. :(
如果键列表太长而无法合理地保存在内存中以进行此类检查，恐怕我会说 sqlce 可能不是适合这项工作的工具。:(
回答by Bennor McCarthy
I'm not sure if this is feasible or not, as I haven't used the Entity Framework, but have you tried running the update first and checking the rowcount -- inserting if no rows were updated? This may be faster than catching exceptions. It's generally a bad practise to use exceptions for control flow, and often slows things down dramatically.
我不确定这是否可行，因为我没有使用过实体框架，但是您是否尝试过先运行更新并检查行数——如果没有行更新则插入？这可能比捕获异常更快。将异常用于控制流通常是一种不好的做法，并且通常会显着减慢速度。
If you can write the SQL directly, then the fastest way to do it would be to get all the data into a temporary table and then update what exists and insert the rests (as in Andrea Bertani's example above). You should get slightly better results by using a left join on the original table in the select in your insert, and excluding any rows with values from the original table that are not null:
如果您可以直接编写 SQL，那么最快的方法是将所有数据放入一个临时表中，然后更新现有数据并插入其余数据（如上面 Andrea Bertani 的示例）。通过在插入的选择中对原始表使用左连接，并排除原始表中具有非空值的任何行，您应该会获得稍微好一点的结果：
INSERT INTO original
SELECT * FROM temp
LEFT JOIN original ON original.id = temp.id
WHERE original.id IS NULL
回答by Bob King
I would recommend using SqlCeResultSet directly. You lose the nice type-safeness of EF, but performance is incrediblyfast. We switched from ADO.NET 2.0-style TypeDataSets to SqlCeResultSet and SqlCeDataReader and saw 20 to 50 times increases in speed.
我建议直接使用 SqlCeResultSet。你失去了 EF 良好的类型安全性，但性能非常快。我们从 ADO.NET 2.0 样式的 TypeDataSets 切换到 SqlCeResultSet 和 SqlCeDataReader，速度提高了 20 到 50 倍。
回答by dkretz
SQL Server compact edition is pretty early in development at this point. Also, depending on your device, memory-disk access can be pretty slow, and SQLCE plus .NET type-safety overhead is pretty intensive. It works best with a pretty static data store.
SQL Server 精简版此时处于开发阶段。此外，根据您的设备，内存磁盘访问可能非常缓慢，并且 SQLCE 加上 .NET 类型安全开销非常大。它最适用于非常静态的数据存储。
I suggest you either use a lighter-weight API or consider SQLite.
我建议你要么使用轻量级的 API，要么考虑 SQLite。
回答by ChulioMartinez
See SqlCeResultSet. For a .NETCF project I removed almost all sql code in favor of this class. Just search for "SqlCeResultSet" here and msdn.
请参见 SqlCeResultSet。对于 .NETCF 项目，我删除了几乎所有 sql 代码以支持此类。只需在此处和 msdn 中搜索“SqlCeResultSet”。
A quick overview:
快速概览：
Open the resultSet. If you need seek (for existence check) you will have to provide an index for the result set.
Seek on the result set & read to check whether you found the row. This is extremely fast even on tables with tens of thousands rows (because the seek uses the index).
Insert or update the record (see SqlCeResultSet.NewRecord).
打开结果集。如果您需要查找（用于存在检查），则必须为结果集提供索引。
查找结果集并阅读以检查您是否找到了该行。即使在具有数万行的表上这也非常快（因为搜索使用索引）。
插入或更新记录（请参阅 SqlCeResultSet.NewRecord）。
We have successfully developed a project with a sqlce database with a main product table with over 65000 rows (read/write with 4 indexes).
我们已经成功开发了一个带有 sqlce 数据库的项目，该数据库的主产品表超过 65000 行（读/写有 4 个索引）。
回答by stevehipwell
When we're using SQL CE (and SQL 2005 Express for that matter) we always call an update first and then call an insert if the udate gives a row count of 0. This is very simple to implement and does not require expensice try..catch blocks for control flow.
当我们使用 SQL CE（以及 SQL 2005 Express）时，我们总是先调用更新，然后如果 udate 给出的行计数为 0，则调用插入。这实现起来非常简单，不需要费用尝试。 .catch 用于控制流的块。
