通过VBA快速上传3000个插入到Teradata [英] Quicken upload of 3000 inserts into Teradata via VBA

问题描述

非常感谢jacouh的答案（ VBA Copy& Paste 3000 rows < a>）我的初始问题。在论坛的帮助下，我现在有以下代码，它从Excel文件中插入2999行。每一行都是$ DB $。$ C INSERT（X，Y，Z，...）VALUES（X1，Y1，Z1，...）到DBNAME.TABLE 中。虽然它的工作，它是痛苦的缓慢。据了解，我可以通过增加缓冲区的大小来提高插入速度，如 http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/Teradata.Client.Provider ~Teradata .Client.Provider.TdConnectionStringBuilder〜ResponseBufferSize.html 。我试图整合并失败。有人建议可能的集成来增加响应缓冲区大小。非常感谢。以前我已经探索过不同的论坛，这绝对是最好的。当然。任何其他想法，比我目前使用的一个想法（在大约15个不同的表格中插入大约3000行），然后请让我知道！）

  Sub Insert_to_TD（）
 
 Dim cn As ADODB.Connection 
 Set cn = New ADODB.Connection 
 Dim rs As ADODB .Recordset 
设置rs =新建ADODB.Recordset 
 Dim cmdsqldata As ADODB.Command 
设置cmdsqldata =新建ADODB.Command 
 
 Dim i，strSQL 
 
 cn.OpenDSN = NNNNNN; Username = XXXXX; Password = YYYYYYY; 
 
 
设置cmdsqldata.ActiveConnection = cn 
 
 cmdsqldata.CommandType = adCmdText 
 cmdsqldata.CommandTimeout = 0 
 
对于我= 1到2999 
 strSQL = ActiveSheet.Cells（i，1）.Value 
 cmdsqldata.CommandText = strSQL 
设置rs = cmdsqldata.Execute（）
下一个
 
 
 End Sub 
  

解决方案

可以并行插入数据行（例如在Teradata SQL Assistant中击中F9）。您可以提交长达1 MB的查询，多个语句以分号分隔，例如：

 INSERT（X ，Y，Z，...）VALUES（X1，Y1，Z1，...）为DBNAME.TABLE; 
 INSERT（X，Y，Z，...）VALUES（X1，Y1，Z1， ...）到DBNAME.TABLE; 
将INSERT（X，Y，Z，...）VALUES（X1，Y1，Z1，...）写入DBNAME.TABLE; 
 INSERT（X， Y，Z，...）VALUES（X1，Y1，Z1，...）转换成DBNAME.TABLE; 
  

将整个字符串一次可能有500行放入strSQL，然后只需要循环并执行6个语句来处理所有的3000.这应该加速你的过程至少10倍。非单线程语言或VBA的一些诡计将允许您一次将多个这些语言发送到Teradata，这可能会增加10倍，具体取决于您的Teradata服务器的容量和允许的连接数。 p>

但是Rob与其他Teradata实用程序在正确的轨道上。有一个原因存在，这就是VBA-teradata操作是很难和缓慢的。

Thank you so much to the answer from jacouh (VBA Copy & Paste 3000 rows) to my initial problem. With the help of the forum, I now have the below code, which inserts 2999 rows from an Excel file. Each row is INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE. Whilst it works, it is painfully slow. As I understand, I could increase the speed of the inserts by increasing the size of the buffers, as described in http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/Teradata.Client.Provider~Teradata.Client.Provider.TdConnectionStringBuilder~ResponseBufferSize.html. I've attempted to incorporate it and failed. Could someone recommend a possible integration to increase the response buffer size. Thank you so much. I've explored different forums previously, and this is definitely the best one. Definitely. Any other ideas, better than the one I'm currently using (inserting approx 3000 rows into about 15 different tables), then please let me know!)

Sub Insert_to_TD()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmdsqldata As ADODB.Command
Set cmdsqldata = New ADODB.Command

Dim i, strSQL

cn.Open "DSN=NNNNNN; Username=XXXXX; Password=YYYYYYY;"


Set cmdsqldata.ActiveConnection = cn

cmdsqldata.CommandType = adCmdText
cmdsqldata.CommandTimeout = 0

For i = 1 To 2999
strSQL = ActiveSheet.Cells(i, 1).Value
cmdsqldata.CommandText = strSQL
Set rs = cmdsqldata.Execute()
Next


End Sub

解决方案

Teradata can insert rows of data in parallel (like hitting F9 in Teradata SQL Assistant). You can submit a query up to 1 MB in length, with multiple statements separated by semicolons, such as:

"INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;    
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;"

put that entire string with maybe 500 rows at a time into strSQL and then you only need to loop through and execute 6 statements to handle all 3000. This should speed up your process at least a factor of 10x. A non-single threaded language or some trickery with VBA will allow you to send more than one of these at a time to Teradata, which could get you an additional 10x, depending on your Teradata server's capacity and the number of connections allowed.

But Rob was on the right track with the other Teradata utilities. There is a reason those exist and it's that VBA-teradata manipulation is hard and slow.

这篇关于通过VBA快速上传3000个插入到Teradata的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！