为什么 SQL 服务器插入这么慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1743496/
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
Why are SQL server inserts so slow?
提问by Peter
I'm trying to insert rows of in-memory data into a table on SQL Server Express 2005. It is running what seems to me very slowly - about 5 seconds per 1000 rows inserted. I am just using a basic "INSERT INTO" command. The slowness does not depend on the table data - it is still slow with a table with one int column and no index. It is nothing to do with my software - it is just as slow running SQL in a loop from Management Studio. There is nothing else accessing the database at the same time. On a 3Ghz Xeon (old I know), this will take about 10 seconds to execute:
我正在尝试将内存数据行插入 SQL Server Express 2005 上的表中。它的运行速度在我看来非常缓慢 - 每插入 1000 行大约 5 秒。我只是使用基本的“INSERT INTO”命令。缓慢与表数据无关 - 对于只有一个 int 列且没有索引的表，它仍然很慢。这与我的软件无关 - 它与 Management Studio 在循环中运行 SQL 的速度一样慢。没有其他东西同时访问数据库。在 3Ghz Xeon（我知道是旧的）上，这将需要大约 10 秒来执行：
declare @i int
set @i = 0
set nocount on
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
end
Is there a better way to insert bulk in-memory data than looping on INSERT? Or some configuration I should change in SQL Server?
有没有比在 INSERT 上循环更好的方法来插入大量内存数据？或者我应该在 SQL Server 中更改一些配置？
回答by Quassnoi
You perform each insert inside its own transaction.
您在其自己的事务中执行每个插入。
Beginning and committing transaction is veryexpensive in SQL Server.
开始，将事务是非常昂贵的SQL Server。
Enclose everything into a single transaction block:
将所有内容封装到一个事务块中：
declare @i int
set @i = 0
set nocount on
BEGIN TRANSACTION
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
end
COMMIT
To generate sample data, you can use a recursive CTE:
要生成示例数据，您可以使用递归CTE：
WITH q (num) AS
(
SELECT 1
UNION ALL
SELECT num + 1
FROM q
WHERE num < 2000
)
INSERT
INTO testdb(testcolumn)
SELECT 1
FROM q
OPTION (MAXRECURSION 0)
, which will be faster.
，这会更快。
回答by Remus Rusanu
1) Log Flush on commit. Every transaction has to ensure the log is flushed to the disk before the commit returns. Every INSERT statement is an implicit transaction. Bulk commit:
1) 提交时刷新日志。每个事务都必须确保在提交返回之前将日志刷新到磁盘。每个 INSERT 语句都是一个隐式事务。批量提交：
declare @i int
set @i = 0
set nocount on
begin transaction
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
if (@i % 1000 = 0)
begin
commit;
begin transaction;
end
end
commit
2) Slow disk. Check the Avg. Disk sec/Transferperformance counter for your data and your log disks.
3) To many indices (unlikely on a test table). Each index is nearly as expensive as a 'table' for inserts.
4) Triggers (again, unlikely)
2) 慢盘。检查平均。数据和日志磁盘的磁盘秒/传输性能计数器。
3）对许多索引（不太可能在测试表上）。每个索引几乎与插入的“表”一样昂贵。
4）触发器（再次，不太可能）
Ultimately, measure. Follow the guidelines of a whitepaper like Troubleshooting Performance Problems in SQL Server 2005if you don't know where to start.
最后，测量。如果您不知道从哪里开始，请遵循白皮书的指导方针，例如对 SQL Server 2005 中的性能问题进行故障排除。
回答by Chad
You have plenty of tools/techniques to get more performance out of this type of work load.
您有很多工具/技术可以从这种类型的工作负载中获得更高的性能。
- If appropriate Bulk Load anything you can.Somethings you can't. Need to run validated against the records, destination table has nullable columns...
- Consider moving complex Data Warehousing/ETL operations to a staging database with no transaction logging (aka simple mode).This will improved performance greatly. Then batch/bulk the data to the destination system.
- Batch non-bulk load insert operations.Commit every n records start with 1,000 and performance tune from there.
- Improve the speed of your disk storage.Smaller faster disk are much better than bigger and slower. The last db performance tuning project I worked on we moved from local disk 10,000 RPM to SAN then back to solid state disk on the server for some operations. Solid State most definitely rocks! But is expensive.
- Use the force, um performance tuning tools for Sql Serverto find less obvious bottle necks. Sometimes the best course of action might be to drop and rebuilt indexes based on what % of records are being inserted/deleted compared to the table size; disable triggers during certain operations; and modifying the sparseness of records in data blocks.
- 如果合适，可以批量加载任何东西。你不能的东西。需要针对记录运行验证，目标表具有可为空的列...
- 考虑将复杂的数据仓库/ETL 操作移动到没有事务日志记录的临时数据库（又名简单模式）。这将大大提高性能。然后将数据批量/批量传输到目标系统。
- 批量非批量加载插入操作。从 1,000 开始提交每 n 条记录并从那里调整性能。
- 提高磁盘存储速度。较小的较快的磁盘比较大且较慢的磁盘要好得多。我参与的最后一个数据库性能调整项目我们从本地磁盘 10,000 RPM 转移到 SAN，然后返回到服务器上的固态磁盘进行一些操作。固态绝对是摇滚！但是很贵。
- 使用 Sql Server 的 force、um 性能调优工具来找到不太明显的瓶颈。有时最好的做法可能是根据与表大小相比插入/删除的记录百分比来删除和重建索引；在某些操作期间禁用触发器；并修改数据块中记录的稀疏性。
回答by AdaTheDev
In addition to indices, if you're actual scenario is as per your example, you could do a set-based approach to insert 2000 records like this:
除了索引之外，如果您的实际场景是根据您的示例，您可以执行基于集合的方法来插入 2000 条记录，如下所示：
INSERT testdb(testcolumn)
SELECT 1
FROM master..spt_values
WHERE number BETWEEN 1 AND 2000
回答by RickNZ
Insert speed is driven by the following things:
插入速度受以下因素驱动：
- The speed of your log disk. In particular, it's important that the log be on a volume by itself, so that disk seeks don't slow things down (can be a 40x effect)
- The structure of your table and associated indexes / keys / triggers, etc.
- The size of your transactions. Larger transactions require fewer round-trips to the log disk, and less associated overhead.
- The size of your command batches. Larger batches are more efficient than many individual ones.
- 日志磁盘的速度。特别是，重要的是日志单独在一个卷上，这样磁盘搜索就不会减慢速度（可能是 40 倍的效果）
- 表的结构和关联的索引/键/触发器等。
- 您的交易规模。更大的事务需要更少的日志磁盘往返，以及更少的相关开销。
- 命令批处理的大小。较大的批次比许多单独的批次更有效。
In case it's of any interest, I go through this in detail in my book (Ultra-Fast ASP.NET), including benchmarks and example code.
如果有任何兴趣，我会在我的书（超快速 ASP.NET）中详细介绍，包括基准测试和示例代码。
回答by Gabriel Magana
I would google to "SQL Server Tuning"... There are many books written on the subject. It is a very hard thing to solve as there are MANY things that affect speed, from query syntax, to RAM allocated to the server, to proportions of allocated RAM (to which part of SQL Server you allocate RAM), to RAID array configuration, and MANY other factors. You can have a database server optimized for insert/updates (OLTP) or for querying (data warehouse type of stuff). In other words, don't expect a single, simple answer to this, even thought your problem seems straightforward.
我会用谷歌搜索“SQL Server Tuning”... 有很多关于这个主题的书籍。这是一件很难解决的事情，因为有很多因素会影响速度，从查询语法到分配给服务器的 RAM，再到分配的 RAM 比例（将 RAM 分配给 SQL Server 的哪一部分），再到 RAID 阵列配置，和许多其他因素。您可以拥有针对插入/更新 (OLTP) 或查询（数据仓库类型的东西）进行优化的数据库服务器。换句话说，即使认为您的问题看起来很简单，也不要期望有一个简单的答案。
This is why you have database server administrators.
这就是您拥有数据库服务器管理员的原因。
Or you could just not sweat the server-side issues and optimize your client-code as much as possible, if timing is not very important to you.
或者，如果时间对您来说不是很重要，您就可以不去解决服务器端问题并尽可能地优化您的客户端代码。
I would look into prepared statements and transactions as a way to begin to optimize. Then look at indexing (if this is a set of inserts that do not happen very often I would consider dropping indices, doing the import, and creating the indices again).
我将研究准备好的语句和事务作为开始优化的一种方式。然后看看索引（如果这是一组不经常发生的插入，我会考虑删除索引，执行导入，然后再次创建索引）。
回答by marr75
Having a clustered index (usually primary key) actually increases insert speed, so verify you have one of those. And running 1000 transactions against a table isn't the fastest way if you can have all of the data at once and insert it into the table (This can be accomplished by using table valued parameters in sql server 2008 or xml parameters in 2005).
拥有聚集索引（通常是主键）实际上会提高插入速度，因此请验证您是否拥有其中之一。如果您可以一次获得所有数据并将其插入表中，那么对表运行 1000 个事务并不是最快的方法（这可以通过使用 sql server 2008 中的表值参数或 2005 中的 xml 参数来实现）。
