字符串循环合并效率问题

yf_992258

总数据大约8万条记录，做一个批量插入，插入数据库前先把插入字符串拼接起来，发现使用蓝色部分拼接字符串很慢，应该是说不能接受的慢，甚至于假死状态，我用我机器分段测试，2000条数据不到1秒，改为5000条数据就要6秒，再往上增加是越来越慢。用红色不拼接字符串，倒是很快的，为什么呢？拼接字符串开销很大？我目前想的办法是按1000条拆分，insert拼接字符串记入数组，这个还可以，我机器大约3秒，再数组循环插入数据库。比如8万条有80条insert拼接字符串，插入80次，前面拼接部分快了，插入数据库慢一点，不知道大家有没有什么好的办法

tt = Timer
lr = Range("a" & Rows.Count).End(xlUp).Row
'lr = 5000
arr = Range("a1:f" & lr)
insert_str = ""
For i = 2 To UBound(arr)
If arr(i, 6) = True Then
  arr(i, 6) = 1
Else
  arr(i, 6) = 0
End If
'If insert_str = "" Then
' insert_str = "('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & sx & ")"
'Else
  insert_str = insert_str & ",('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
'End If
Next
MsgBox Timer - tt

arsong

我艹，我只想知道，你闲着没事干把八万条记录串起来干啥子

fdexcel

你干嘛不换个思路，用别的方式，如果实在用插入语句方式，一行数据一条语句。

yf_992258

arsong 发表于 2023-3-9 14:08
我艹，我只想知道，你闲着没事干把八万条记录串起来干啥子

题干写了，为了快速插入数据库，当然逐条插入肯定是可以的

yf_992258

fdexcel 发表于 2023-3-9 14:16
你干嘛不换个思路，用别的方式，如果实在用插入语句方式，一行数据一条语句。

题干写了，已经换思路了，测试下来还能接受

yf_992258

网上找了相类似情况的文章，有需要的可以参考
https://www.jianshu.com/p/d8c08a7f2099
以下是按我之前的思路，分批写insert字符串，循环插入，7万多条数据我的机器插入到内网服务器mysql库大约12秒，还是可以接受了。
按1000条分割insert语句12秒，10000条就要25秒了。

Dim xx
tt = Timer
lr = Range("a" & Rows.Count).End(xlUp).Row
'lr = 4000
arr = Range("a1:f" & lr)
fg = Cells(1, 7) '----------------输入准备分割记录数，按1000条记录分割效率比较高
ls = lr / fg '1000
If ls = Fix(ls) Then
sjxb = ls
Else
sjxb = Fix(ls) + 1
End If
ReDim xx(1 To sjxb)
'insert_str = ""

If UBound(arr) > fg Then
szsx = fg
Else
szsx = UBound(arr)
End If

For i = 2 To szsx 'UBound(arr)
If arr(i, 6) = True Then
  arr(i, 6) = 1
Else
  arr(i, 6) = 0
End If
arr(i, 2) = Replace(arr(i, 2), "'", "\'")
arr(i, 3) = Replace(arr(i, 3), "'", "\'")

temp = "('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
If xx(1) = "" Then
  xx(1) = temp '"('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
Else
  'xx(1) = xx(1) & ",('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
  xx(1) = xx(1) & "," & temp
End If
Next


For j = 2 To sjxb
qshs = (j - 1) * fg + 1
jzhs = j * fg
If jzhs > UBound(arr) Then jzhs = UBound(arr)
For i = qshs To jzhs
  If arr(i, 6) = True Then
   arr(i, 6) = 1
  Else
   arr(i, 6) = 0
  End If
  arr(i, 2) = Replace(arr(i, 2), "'", "\'")
  arr(i, 3) = Replace(arr(i, 3), "'", "\'")
  temp = "('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
  If xx(j) = "" Then
   xx(j) = temp '"('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
  Else
   'xx(j) = xx(j) & ",('" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "','" & arr(i, 5) & "'," & arr(i, 6) & ")"
   xx(j) = xx(j) & "," & temp
  End If
Next
Next
'MsgBox Timer - tt

Call lj_mysql '-----------连接MYSQL数据库
For k = 1 To sjxb
Set rst = New ADODB.Recordset
With rst
  .Open "insert into inventory (cinvcode,cinvname,cinvstd,cinvccode,cinvm_unit,sx) values " & xx(k), conn, 1, 3, adCmdText
End With
Next
conn.Close
Set rst = Nothing
Set conn = Nothing
Erase arr
Erase xx
MsgBox "O K!" & Timer - tt

tanglf188

试试用Join呢

1.     tt = Timer
   
2.     Dim d As Object, arr, brr, i&
   
3.     Set d = CreateObject("Scripting.Dictionary")
   
4.     lr = Range("a" & Rows.Count).End(xlUp).Row
   
5.     arr = Range("a1:f" & lr)
   
6.     insert_str = ""
   
7.     For i = 1 To UBound(arr)
   
8.         If arr(i, 6) = True Then
   
9.           arr(i, 6) = 1
   
10.         Else
    
11.           arr(i, 6) = 0
    
12.         End If
    
13.         d(i) = Join(Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5), arr(i, 6)), ",")
    
14.     Next
    
15.     arr = d.items
    
16.     insert_str = Join(arr, "),(")
    
17.     insert_str = "(" & insert_str & ")"
    
18.     MsgBox Timer - tt
    

复制代码

huanghai22

你都用数组了，干嘛不用join函数拼接字符串？

yf_992258

谢谢楼上2位，使用join确实快，解决了一次性拼接问题。测试下来7万多条记录一次性插入局域网mysql数据库，5秒左右