如何快速向SQL Server 2008插入数据

Question

如何快速向SQL Server 2008插入数据

sql-serverperformance

32

我有一个涉及将设备数据直接记录到SQL表中的项目。

在写入SQL Server（顺便提一下，我用的是2008 Express版本）之前，我的代码很少进行处理。

通常，我使用SqlHelper类的ExecuteNonQuery方法，并传递存储过程名称和存储过程所需的参数列表。

这非常方便，但我需要更快的方法来完成这个任务。

谢谢。

- Charles Okwuagwu

我必须一个接一个地记录每个输出，不能收集多个输出并批量记录它们。 - Charles Okwuagwu

7个回答

6

批量插入是最快的，因为它记录最少。

.NET还有SqlBulkCopy类

- SQLMenace

我必须逐个记录每个输出，不能收集多个输出并批量记录它们。 - Charles Okwuagwu

3

以下是使用表变量插入大量记录的好方法...

但最好每次限制到1000条记录，因为表变量在内存中。

在这个例子中，我将向一个包含3个字段的表中插入2条记录 - CustID，Firstname，Lastname。

--first create an In-Memory table variable with same structure
--you could also use a temporary table, but it would be slower

declare @MyTblVar table (CustID int, FName nvarchar(50), LName nvarchar(50))

insert into @MyTblVar values (100,'Joe','Bloggs')

insert into @MyTblVar values (101,'Mary','Smith')

Insert into MyCustomerTable

Select * from @MyTblVar

- user2696172

4

这真的比直接插入数据库表更有效吗？（这是一个真正的问题，而不是批评...） - Mike Gledhill

2

通常情况下，这是通过BULK INSERT完成的。简单来说，您需要准备一个文件，然后发出BULK INSERT语句，SQL Server会以最快的方式将文件中的所有数据复制到表中。

它确实有一些限制（例如，如果您可能存在要更新的行，则无法执行“更新或插入”类型的行为），但是如果您能够克服这些限制，那么您不太可能找到任何更快的方法。

- Dean Harding

我必须单独记录每个设备的输出，这不包括批量选项。 - Charles Okwuagwu

“单独记录每个设备的输出”是什么意思？ - Dean Harding

我的意思是我必须逐个记录每个输出，不能将多个输出收集起来批量记录。 - Charles Okwuagwu

2

如果你是指从.NET进行操作，那么可以使用 SqlBulkCopy。

- Mitch Wheat

1

我必须单独记录每个设备的输出，这不包括批量选项。 - Charles Okwuagwu

@CharlesO：我不知道那是什么意思。Bulk与多个设备没有关系！ - Mitch Wheat

我的意思是我必须一个接一个地记录每个输出，我不能将多个输出收集起来批量记录。 - Charles Okwuagwu

2

可能会影响插入速度的因素包括索引以及对同一表进行的读取或更新（锁定）。您可以通过避免这些因素，并将单独的事务插入到一个没有索引或其他活动的临时表中，从而加快类似情况的速度。然后稍微不那么频繁地将临时表批量导入到主表中。

- Joel Coehoorn

@Mitch：这对我来说是新闻，你能给我们一个例子吗？ - Aaronaught

@Joel：你的建议很有道理。基本上，我保持一个ADO.net连接打开，并继续使用sqlcommand对象以与设备产生的速度相同的速度插入记录。但是： 1）在这种情况下，原始的SQL插入是否比使用存储过程更好？2）这个保持表需要有一个聚集索引，像一个自动递增的ID列吗？3）假设我每2秒运行一个sqlAgent脚本来批处理保持表到主表，复制每个批次后保持表上的删除是否会进一步降低性能？ - Charles Okwuagwu

@Aaronaught - 通过帮助其他查询更快地运行，从而减少表上的锁定。 - Joel Coehoorn

3

索引可以加速检查外键约束。 - meriton

1

它只能以您的SP运行速度为准确参考。确保表格被正确索引，如果您有一个聚集索引，请确保它具有窄、唯一、递增的键。确保其余索引和约束（如果有）没有太多的开销。

在ADO.NET层中不应该看到太多的开销（我不会使用任何其他.NET库来代替SQLCommand）。您可以尝试使用ADO.NET Async方法，以便在不阻塞应用程序中的单个线程的情况下排队执行多个对存储过程的调用（这可能比任何其他方法都能释放更多吞吐量，就像有多个机器插入数据库一样）。

除此之外，您确实需要告诉我们更多关于您的需求。

- Cade Roux

坚定的建议是，只使用纯ADO.net，并尽可能长时间保持连接。为存储过程构建参数，然后调用存储过程并传递参数，对于简单的插入操作来说似乎很繁琐。您是否建议直接从ADO.net发送插入语句？ - Charles Okwuagwu

@CharlesO - 我会使用SQLCommand，添加参数并执行ExecuteNonQuery（我不会发送“INSERT INTO blah VALUES（blah'）”字面字符串）-我肯定会考虑使用异步版本：BeginExecuteNonQuery（http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.beginexecutenonquery.aspx），并在程序内部设置一些活动插入的限制和队列。然后，您可以查看它是否能够处理您预期的工作量，或者是否需要更广泛的存储和转发。 - Cade Roux

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Remus Rusanu · Accepted Answer

使用INSERT语句或存储过程的ExecuteNonQuery方法可以让您在Express上实现每秒数千个插入。4000-5000 / 秒非常容易实现，我可以证明这一点。

通常会减慢单个更新的是日志刷新的等待时间，您需要考虑这一点。最简单的解决方案是批量提交。例如，每1000个插入或每秒提交一次。这将填充日志页面，并将日志刷新等待的成本分摊到事务中的所有插入操作中。

使用批处理提交，你可能会出现磁盘日志写入性能瓶颈，而无法做任何事情除了更换硬件（对日志进行raid 0条带化）。

如果您遇到早期的瓶颈（不太可能），那么您可以尝试批处理语句，即发送一个具有多个插入操作的单个T-SQL批处理。但这很少有回报。

当然，您需要将写入的大小减少到最小，也就是说，将表的宽度减少到最小所需列，消除非聚集索引，消除不必要的约束条件。如果可能，请使用堆而不是聚集索引，因为堆插入要比聚集索引快得多。

使用普通的INSERT语句和ExecuteNoQuery方法进行批处理提交，几乎不需要使用快速插入接口（例如SqlBulkCopy）。因为批处理提交会比部署批量插入更快地耗尽驱动器的顺序写入吞吐量。对于快速连接到SAN的机器，需要使用批量插入。您提到了Express，所以可能不需要这样做。人们普遍认为相反是正确的，但这只是因为他们没有意识到批量插入给他们带来了批量提交，而不是批量插入加速了操作。

与任何性能测试一样，请确保消除随机性，并预分配数据库和日志，您不希望在测试测量或生产期间出现数据库或日志增长事件，那太业余了。