为什么 SQL 服务器插入这么慢? [英] Why are SQL server inserts so slow?
问题描述
我正在尝试将内存数据行插入 SQL Server Express 2005 上的表中.它的运行速度在我看来非常缓慢 - 每插入 1000 行大约 5 秒.我只是使用基本的INSERT INTO"命令.缓慢与表数据无关 - 对于只有一个 int 列且没有索引的表,它仍然很慢.这与我的软件无关 - 它与 Management Studio 在循环中运行 SQL 的速度一样慢.没有其他东西同时访问数据库.在 3Ghz Xeon(我知道是旧的)上,这将需要大约 10 秒的时间来执行:
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:
declare @i int
set @i = 0
set nocount on
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
end
有没有比 INSERT 循环更好的方法来插入大量内存数据?或者我应该在 SQL Server 中更改一些配置?
Is there a better way to insert bulk in-memory data than looping on INSERT? Or some configuration I should change in SQL Server?
推荐答案
您在自己的事务中执行每个插入.
You perform each insert inside its own transaction.
在SQL Server
中开始和提交事务非常开销很大.
Beginning and committing transaction is very expensive in 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
要生成示例数据,您可以使用递归CTE
:
To generate sample data, you can use a recursive 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)
,这样会更快.
这篇关于为什么 SQL 服务器插入这么慢?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!