将Excel表数据传输到SQL 2008R2的最快方法 [英] Fastest way to transfer Excel table data to SQL 2008R2

问题描述

有人知道使用外部实用程序(即bcp)将数据表和Excel表(VBA阵列)中的数据以最快的方式从SQL 2008中以 导入到表中吗? 请记住，我的数据集通常是6500-15000行，大约150-250列；最后我在自动VBA批处理脚本中传输了其中的20-150.

Does anyone know the fastest way to get data from and Excel table (VBA Array) to a table on SQL 2008 without using an external utility (i.e. bcp)? Keep in mind my datasets are usually 6500-15000 rows, and about 150-250 columns; and I end up transferring about 20-150 of them during an automated VBA batch script.

我尝试了几种从Excel表(VBA)到SQL 2008获取大量数据的方法.下面列出了这些方法:

I have tried several methods for getting large amounts of data from an Excel table (VBA) to SQL 2008. I have listed those below:

方法1.将表传递到VBA Array中并发送到存储过程(ADO) -发送到SQL的速度很慢

Method 1. Pass table into VBA Array and send to stored procedure (ADO) -- Sending to SQL is SLOW

方法2.创建断开连接的RecordSet，然后加载它，然后进行同步. -发送到SQL的速度很慢

Method 2. Create disconnected RecordSet load it, then sync. -- Sending to SQL VERY SLOW

方法3.将表放入VBA数组中，遍历该数组并连接(使用定界符)，然后发送到存储过程. -发送到SQL SLOW，但比方法1或2更快.

Method 3. Put table into VBA array, loop though the array and concatenate(using delimiters) then send to stored procedure. -- Sending to SQL SLOW, but faster than Method 1 or 2.

方法4.将表放入VBA数组中，遍历该数组并进行连接(使用定界符)，然后使用ADO recordset .addnew命令放置每一行. -发送到SQL的速度非常快(比方法1-3快20倍)，但是现在我将需要使用单独的过程拆分数据，这将增加大量的等待时间.

Method 4. Put table into VBA array, loop though the array and concatenate(using delimiters) then place each row with ADO recordset .addnew command. --Sending to SQL very FAST (about 20 times faster than methods 1-3), but now I will need to split that data using a separate procedure, which will add significant wait time.

方法5.将表放入VBA数组中，序列化为XML，以VARCHAR的形式发送到存储过程，并在存储过程中指定XML. -发送到SQL的速度慢(比方法1或2慢100倍)

Method 5. Put table in VBA array, serialize into XML, send to stored procedure as VARCHAR and specify XML in stored procedure. --Sending to SQL INCREDIBLY SLOW (about 100 times slower than methods 1 or 2)

我想念什么吗?

推荐答案

没有最快的方法，因为它取决于许多因素.确保已配置和优化了SQL中的索引.许多索引将破坏插入/更新性能，因为每个插入都需要更新索引.确保仅与数据库建立一个连接，并且在操作过程中不要打开/关闭该数据库.当服务器处于最小负载时运行更新.您没有尝试过的唯一其他方法是使用ADO Command对象，然后发出直接INSERT语句.当使用记录集对象的"AddNew"方法时，请确保在插入的末尾仅发出一个"UpdateBatch"命令.除此之外，VBA的运行速度只能与接受输入的SQL Server一样快.

There is no single fastest way, as it's dependent on a number of factors. Make sure the indexes in SQL are configured and optimized. Lots of indexes will kill insert/update performance since each insert will need to update the index. Make sure you only make one connection to the database, and do not open/close it during the operation. Run the update when the server is under minimal load. The only other method you haven't tried is to use a ADO Command object, and issue a direct INSERT statement. When using the 'AddNew' Method of the recordset object, be sure to issue only one 'UpdateBatch' Command at the end of the inserts. Short of that, the VBA can only run as fast as the SQL server accepting the inputs.

好像您已经尝试了一切.在SQL Server中，还有一种称为大容量日志记录"的恢复模式，该模式可以减少向事务日志中写入大量内容的开销.可能是值得研究的东西.这可能很麻烦，因为它需要稍微摆弄数据库恢复模型，但这对您可能很有用.

Seems like you've tried everything. There is also what is known as 'Bulk-Logged' recovery mode in SQL Server, that reduces the overhead of writting so much to the transaction log. Might be something worth looking into. It can be troublesome since it requires fiddling with the database recovery model a bit, but it could be useful for you.

这篇关于将Excel表数据传输到SQL 2008R2的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！