如何对 SQL Server 2008 进行非常快速的插入 [英] How to do very fast inserts to SQL Server 2008

问题描述

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

I have a project that involves recording data from a device directly into a sql table.

在写入sql server之前我在代码中做的处理很少(顺便说一下2008 express)

I do very little processing in code before writing to sql server (2008 express by the way)

通常我使用 sqlhelper 类的 ExecuteNonQuery 方法并传入存储的过程名称和 SP 期望的参数列表.

typically i use the sqlhelper class's ExecuteNonQuery method and pass in a stored proc name and list of parameters that the SP expects.

这很方便，但我需要一种更快的方法.

This is very convenient, but i need a much faster way of doing this.

谢谢.

推荐答案

ExecuteNonQuery 与 INSERT 语句，甚至是存储过程，将使您在 Express 上达到每秒数千次插入的范围.4000-5000/秒很容易实现，我知道这是事实.

ExecuteNonQuery with an INSERT statement, or even a stored procedure, will get you into thousands of inserts per second range on Express. 4000-5000/sec are easily achievable, I know this for a fact.

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

What usually slows down individual updates is the wait time for log flush and you need to account for that. The easiest solution is to simply batch commit. Eg. commit every 1000 inserts, or every second. This will fill up the log pages and will amortize the cost of log flush wait over all the inserts in a transaction.

使用批量提交，您可能会遇到磁盘日志写入性能的瓶颈，除了更改硬件(在日志上进行 raid 0 条带化)之外，您无能为力.

With batch commits you'll probably bottleneck on disk log write performance, which there is nothing you can do about it short of changing the hardware (going raid 0 stripe on log).

如果您遇到了较早的瓶颈(不太可能)，那么您可以查看批处理语句，即.发送一个包含多个插入的单个 T-SQL 批处理.但这很少有回报.

If you hit earlier bottlenecks (unlikely) then you can look into batching statements, ie. send one single T-SQL batch with multiple inserts on it. But this seldom pays off.

当然，您需要将写入的大小减少到最小，这意味着将表的宽度减少到最少需要的列，消除非聚集索引，消除不需要的约束.如果可能，请使用堆而不是聚集索引，因为堆插入比聚集索引快得多.

Of course, you'll need to reduce the size of your writes to a minimum, meaning reduce the width of your table to the minimally needed columns, eliminate non-clustered indexes, eliminate unneeded constraints. If possible, use a Heap instead of a clustered index, since Heap inserts are significantly faster than clustered index ones.

几乎不需要使用快速插入接口(即SqlBulkCopy).对批量提交使用普通的 INSERTS 和 ExecuteNoQuery，您将比部署批量插入的需要更快地耗尽驱动器顺序写入吞吐量.在快速连接 SAN 的机器上需要批量插入，而您提到了 Express，因此可能并非如此.有一种相反的看法，但这仅仅是因为人们没有意识到批量插入给了他们批量提交，而且是批量提交加快了思考速度，而不是批量插入.

There is little need to use the fast insert interface (ie. SqlBulkCopy). Using ordinary INSERTS and ExecuteNoQuery on batch commits you'll exhaust the drive sequential write throughput much faster than the need to deploy bulk insert. Bulk insert is needed on fast SAN connected machines, and you mention Express so it's probably not the case. There is a perception of the contrary out there, but is simply because people don't realize that bulk insert gives them batch commit, and its the batch commit that speeds thinks up, not the bulk insert.

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

As with any performance test, make sure you eliminate randomness, and preallocate the database and the log, you don't want to hit db or log growth event during test measurements or during production, that is sooo amateurish.

这篇关于如何对 SQL Server 2008 进行非常快速的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！