=======VB循环插入数据速度很慢（增加100分求救）=========

Question

参考贴：http://topic.csdn.net/u/20090702/14/33b6fc3b-20bf-4d88-a2c3-8082e43c4fc2.html?seed=252373240&r=58109003#r_58109003
大家好，我用VB读取Excel中的数据，然后把里面的数据循环写入到远程服务器的SQLserver2000中的表中。 如果在本地执行，速度挺快的，但是一旦写入到远程数据库，速度很慢很慢啊。我的网速是adsl 1M的，也不能这么慢啊。 
请问各位有没有好的办法？ ============================================== 
附上程序： 
Dim excel_app    As Object 
          Dim excel_sheet    As Object           Set excel_app = CreateObject("excel.application")                                          'excel对象 
          Set excel_app = New Excel.Application 
          excel_app.Workbooks.Open FileName:=Text1.Text 
          If Val(excel_app.Application.Version) >= 8 Then                                                '检查excel文件的版本 
          Set excel_sheet = excel_app.ActiveSheet 
          Else 
          Set excel_sheet = excel_app 
          End If           '''创建sql表格           Dim Row 
  
          For Row = 2 To 1000 '取Excel表格中的2-1000行 
          
          DoEvents 
          ’写入到远程的sqlserver中 
Dim rs2 As New ADODB.Recordset 
            rs2.Open "select top 1 * from t_client", conn, 1, 3 
            rs2.AddNew 
            rs2.Fields("c_id") = c_id 
            rs2.Fields("c_name") = c_name 
            rs2.Fields("c_intime") = Now() 
            rs2.Fields("c_inman") = "admin" 
            rs2.Fields("c_valid") = 1 
            rs2.Update 
            rs2.Close 
'写入结束 Row = Row + 1                    '读取下一行数据 
          Next 
          
excel_app.Quit           Set excel_app = Nothing 
          Set excel_sheet = Nothing 

Answer 1

我建议你把 
 rs2.Open "select top 1 * from t_client", conn, 1, 3 
 close 什么的 放在你你循环的外面啦 
   尽量的减少循环的类容 速度肯定就不会慢了啊· 你看循环一次就打开一次 关闭一次不慢那才是怪事··

Answer 2

你是怎样连接远程SQL数据库的?

Answer 3

可以把excel作为数据库，两个数据库直接连接读写数据Private Sub Command1_Click()
    Dim cn As ADODB.Connection    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\f1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"    cn.Execute "Insert Into [;database=" & App.Path & "\mydb2.mdb].[f2](id,item1,item2)   Select id,item1,item2 From [Sheet1$]"    cn.Close
    Set cn = Nothing
End Sub

Answer 4

Dim excel_app    As Object 
          Dim excel_sheet    As Object           Set excel_app = CreateObject("excel.application")                                          'excel对象 
          Set excel_app = New Excel.Application 
          excel_app.Workbooks.Open FileName:=Text1.Text 
          If Val(excel_app.Application.Version) >= 8 Then                                                '检查excel文件的版本 
          Set excel_sheet = excel_app.ActiveSheet 
          Else 
          Set excel_sheet = excel_app 
          End If           '''创建sql表格           Dim Row 
  
          For Row = 2 To 1000 '取Excel表格中的2-1000行 
          
          DoEvents 
          ’写入到远程的sqlserver中             conn.excute "insert into t_client select '" & c_id &"','" & c_name &"','" & Now() & "','admin','1'"
'写入结束 Row = Row + 1                    '读取下一行数据 
          Next 
          
excel_app.Quit           Set excel_app = Nothing 
          Set excel_sheet = Nothing 

Answer 5

sql = sql & "insert into table(...) values(...) "cn.execute

Answer 6

SQL不是太长时，可以讲SQL拼接起来只执行一次。sql = sql & "insert into table_name(col1,col2..) values (value1,value2) "
cn.execute优点：
相对于逐条执行速度有很大提升。缺点：
1.当SQL语句太长时，对超时时间要设置的较长。
2.有时会不可用，比如修改表格结构时不能用。

Answer 7

Dim rs2 As New ADODB.Recordset 
rs2.Open "select * from t_client where 0=1", conn, 3, 4 
For Row = 2 To 1000            
         rs2.AddNew 
         rs2.Fields("c_id") = c_id 
         rs2.Fields("c_name") = c_name 
         rs2.Fields("c_intime") = Now() 
         rs2.Fields("c_inman") = "admin" 
         rs2.Fields("c_valid") = 1 
Next 
rs2.updatebatch adAffectAllChapters
rs2.close不要一个一个的Update啦，记录数目不多的时候用UpdateBatch做一次批量更新就好了！

Answer 8

将excel作为数据源，用一个insert直接插入到库里好了

Answer 9

如果把rs.open和close放倒循环外面，依然不能解决问题
那就按3楼的方法

Answer 10

给这个例子，lz模仿会更容易Private Sub Command1_Click()
    Dim cn As ADODB.Connection    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\mydb2.mdb;;Jet OLEDB:Database Password=111"    cn.Execute "Insert Into [f2] Select * From [Sheet1$] In '" & App.Path & "\f1.xls' 'EXCEL 8.0;'"    cn.Close
    Set cn = NothingEnd Sub

Answer 11

ping服务器看看，是否有断点? 
服务器是否有问题？我这里都是远程的，要都是你这样还不疯了

Answer 12

把下列语句:
rs2.Open "select top 1 * from t_client", conn, 1, 3 
            rs2.AddNew 
            rs2.Fields("c_id") = c_id 
            rs2.Fields("c_name") = c_name 
            rs2.Fields("c_intime") = Now() 
            rs2.Fields("c_inman") = "admin" 
            rs2.Fields("c_valid") = 1 
            rs2.Update 
            rs2.Close
改为
sqlstr="insert into t_client(field1,field2,....) values(value1,value2,.....)
cn.execute(sqlstr)
连接的内容自己写了.
就可以了,这样可以大大的提高速度.

Answer 13

cn.Execute "Insert Into [f2] Select * From [Sheet1$] In '" & App.Path & "\f1.xls' 'EXCEL 8.0;'"

Answer 14

一、使用一个字段分隔符和记录分隔符，读取本地的EXCEL文件，将需要插入的数据连接成一个字符串。
二、创建一个存储过程，接收该字符串，然后在存储过程里将字符串分解成相应的字段和记录，再插入到SQL数据库中。
我曾按此思路写过这样的存储过程，代码有点长，楼主参考一下：
CREATE PROCEDURE PROC_INSERT(@Table varchar(100),@strData nvarchar(10000),@strCondition nvarchar(100)='')
AS
--修改日期:2006年11月27日--
IF LEN(@strData)=0 RETURN 0 DECLARE @strSQL nvarchar(4000)
DECLARE @strRow nvarchar(256),@strCol varchar(64)
DECLARE @I int,@J int,@colid int
DECLARE @table_info TABLE(colid smallint,xtype smallint,name varchar(28)) SET NOCOUNT ON
INSERT INTO @table_info SELECT colid,xtype,name FROM syscolumns WHERE id=object_id(@Table) ORDER BY colid SET @strSQL=N''
SET @strData=REPLACE(@strData,'￥','')+'┛' SET @strSQL=@strSQL+' BEGIN TRANSACTION TRAN_PROC_INSERT '
SET @strSQL=@strSQL+' SAVE TRANSACTION TRAN_PROC_INSERT '
SET @strSQL=@strSQL+' BEGIN '
IF CHARINDEX('WHERE ',@strCondition)>0
SET @strSQL=@strSQL+' DELETE FROM '+@Table+' '+@strCondition+' ' SET @I=CHARINDEX('┛',@strData)
WHILE @I>0
BEGIN

SET @strSQL=@strSQL+'INSERT INTO '+@Table+' VALUES('
SET @strRow=LEFT(@strData,@I-1)+'┇'
SET @strData=RIGHT(@strData,LEN(@strData)-@I)
SET @J=CHARINDEX('┇',@strRow)
SET @colid=1
WHILE @J>0
BEGIN
SET @strCol=RTRIM(LEFT(@strRow,@J-1))
SET @strRow=RIGHT(@strRow,LEN(@strRow)-@J)
IF LEN(@strCol)=11 AND CHARINDEX('年',@strCol)>0 AND CHARINDEX('月',@strCol)>0
SET @strCol=REPLACE(REPLACE(REPLACE(@strCol,'日',''),'月','-'),'年','-')
ELSE IF ISNUMERIC(@strCol)=1
SET @strCol=REPLACE(@strCol,',','')
ELSE IF @strCol='True'
SET @strCol=1
ELSE IF @strCol='False'
SET @strCol=0
SELECT @strCol= CASE
WHEN xtype IN (231)
THEN ''''+CAST(@strCol as VARCHAR)+''''
WHEN xtype IN (48,52,56,59,60,62,104,106,108,122,127)
THEN CAST(@strCol as VARCHAR)
WHEN xtype IN (58,61)
THEN ''''+CAST(@strCol as VARCHAR)+''''
WHEN xtype IN (167)
THEN ''''+CAST(@strCol as VARCHAR)+''''
WHEN xtype IN (175)
THEN ''''+CAST(@strCol as VARCHAR)+''''
WHEN xtype IN (239)
THEN ''''+CAST(@strCol as VARCHAR)+''''
END
FROM @table_info WHERE colid=@colid

SELECT @strSQL=@strSQL+@strCol+',' SET @colid=@colid+1
SET @J=CHARINDEX('┇',@strRow)
END
SET @strSQL=LEFT(@strSQL,LEN(@strSQL)-1)+');' SET @I=CHARINDEX('┛',@strData)
END SET @strSQL=@strSQL+' END '
SET @strSQL=@strSQL+' COMMIT TRANSACTION TRAN_PROC_INSERT '
SET @strSQL=@strSQL+' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION TRAN_PROC_INSERT ' EXECUTE sp_executesql @strSQL
SET NOCOUNT OFF

Answer 15

Private Sub Command1_Click()
    Dim cn As ADODB.Connection    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\mydb2.mdb;;Jet OLEDB:Database Password=111"    cn.Execute "Insert Into [f2] Select * From [Sheet1$] In '" & App.Path & "\f1.xls' 'EXCEL 8.0;'"    cn.Close
    Set cn = NothingEnd Sub

Answer 16

1、在FOR循环的外面启动事务，循环结束后提交事务
2、改为使用Insert into 语句插入数据，不要使用记录集的Insert,至少不能每次都读取一下数据。
或者使用批量更新，先把数据都Insert到记录集，最后一起更新。

Answer 17

可以
MS SQLif not exists(select top 1 1 from t_client where c_id='001')
INSERT INTO t_client (c_name,c_intime,c_inman,c_valid) values (......)

Answer 18

lz你要知道，adsl的下行和上行是两码事，你的1M带宽是adsl的下行带宽，上行一般就是64K
64K的带宽，能有多快啊。

Answer 19

如果是一次性导数据，可以用SQL的导入导出功能来完成

Answer 20

如果是导入远程sqlserver，与程序的关系不大。主要还在于你的网速。
28楼的建议可行。

Answer 21

有一点楼主需要考虑的就是网络延时！！电信的ADSL默认的上传速度只有50~60KB左右，再考虑到服务器性能、网络塞车等因素，速度也不会快到哪去了~

Answer 22

感谢各位的帮助，我再详细的写一下我的程序的整个流程：1.从excel文档中循环取出数据（c_id,c_name等字段）
2.根据取出的数据c_id，查询数据库表t_client中是否存在该数据
3.如果不存在则插入这条数据到t_client中我进行的工作就是这几个步骤，请问能一次性的写入吗？如果一次性的写入，那么第二步怎么进行？谢谢！

Answer 23

SQL有个数据导入导出的功能，你看看应该会明白的
不过这样有个注意点就是，你的EXCEL文件必须在SQL服务器上

Answer 24

先用本地數據庫執行EXCEL的導入動作，當把Excel成功導入到本地SQL Server數據庫後，再用SQL Server對遠程數據庫導入，你可以自己生成DTS腳本執行，或者使用BCP技術去導入，非常快的。