SQL Server 中插入与更新的成本 [英] Cost of Inserts vs Update in SQL Server

问题描述

我有一张超过一百万行的表格.此表用于索引 tiff 图像.每个图像都有像 date、number 等字段.我有用户以 500 个批次为这些图像编制索引.我需要知道先插入 500 行是否更好然后执行 500 次更新，或者当用户完成索引时，对所有数据执行 500 次插入.很重要的一点是，如果我一开始做 500 次插入，这次对我来说是免费的，因为我可以在前一天晚上做.

I have a table with more than a millon rows. This table is used to index tiff images. Each image has fields like date, number, etc. I have users that index these images in batches of 500. I need to know if it is better to first insert 500 rows and then perform 500 updates or, when the user finishes indexing, to do the 500 inserts with all the data. A very important thing is that if I do the 500 inserts at first, this time is free for me because I can do it the night before.

所以问题是:插入还是插入和更新更好，为什么?我为每个图像定义了一个 id 值，并且我在字段上还有其他索引.

So the question is: is it better to do inserts or inserts and updates, and why? I have defined a id value for each image, and I also have other indices on the fields.

推荐答案

Sql server 中的更新导致重影行 - 即 Sql 划掉一行并放入新的一行.划掉的行稍后被删除.

Updates in Sql server result in ghosted rows - i.e. Sql crosses one row out and puts a new one in. The crossed out row is deleted later.

插入和更新都会以这种方式导致页面拆分，它们都有效地添加"数据，只是更新首先将旧内容标记出来.

Both inserts and updates can cause page-splits in this way, they both effectively 'add' data, it's just that updates flag the old stuff out first.

在此更新之上，需要先查找行，对于大量数据，这可能比更新花费的时间更长.

On top of this updates need to look up the row first, which for lots of data can take longer than the update.

插入总是更快，特别是如果它们是有序的或者基础表没有聚集索引.

Inserts will just about always be quicker, especially if they are either in order or if the underlying table doesn't have a clustered index.

当向表中插入大量数据时，请查看当前索引 - 它们可能需要一段时间来更改和构建.在索引中间添加值总是比较慢.

When inserting larger amounts of data into a table look at the current indexes - they can take a while to change and build. Adding values in the middle of an index is always slower.

你可以把它想象成添加到地址簿:Z先生可以添加到最后一页，而你必须在中间为M先生找到空间.

You can think of it like appending to an address book: Mr Z can just be added to the last page, while you'll have to find space in the middle for Mr M.

这篇关于SQL Server 中插入与更新的成本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！