返回列表 上一主題 發帖

[發問] excel vba (插入資料)insert 進 sqlite 緩慢問題

[發問] excel vba (插入資料)insert 進 sqlite 緩慢問題

本帖最後由 joey0415 於 2013-8-6 22:21 編輯

之前曾經練習insert把資料從sheet1中,放進sqlite資料表中,
後來在sheet1中加入多行資料,也想將數十從的資料透過for 回圈放進資料資料表中
雖然按下去也可以執行,但是速度慢到無法想像,才500筆的資料就要數十秒之多
joey.zip (440 Bytes)
我的表(home)
20130806-174.gif
2013-8-6 22:19


上網查過資料,發現sqlite若要大量insert時,可以透過begin 與commit的方法,先打開(begin),再一起sql語法,最後一次寫入(commit)
下面是小弟網路上查到的vba語法,看樣子是對的,不過還是會報錯,最後一個是小弟可以成功,但是速度慢的方法,不知道可以幫忙修改成快的方法嗎?


20130806-172.gif
2013-8-6 22:13



謝謝

網路上的語法(有begin    commit)
http://www.mrexcel.com/forum/excel-questions/435486-sqlite-excel-visual-basic-applications-4.html
  1. sub Batch_Insert_Statement
  2. For i = 1 To 100000
  3. xsql = xsql & "Insert into tablename (ProductCode, UnitsSold, DateSold) values '" & Trim(MyRecordset!ProductCode) & "', '" & MyRecordset!UnitsSold &"', '" &
  4. Format(MyRecordset!DateSold, "YYYY-MM-DD HH:MM:SS") & "'; "
  5. Next i
  6. do_sql (xsql)
  7. end sub

  8. ' Insert records inside a SQLite transaction - Open DB - begin - batch - Commit
  9. Sub do_sql(xsql As String)
  10. Set filecnn = New_c.Connection("C:\PathToYour\Database.sql")
  11. filecnn.Execute "Begin"
  12. filecnn.Execute xsql
  13. filecnn.Execute "Commit"
  14. Set filecnn = Nothing
  15. End Sub
複製代碼
我的做法,但速度慢
  1. [b]Sub vba_sqlite_inser批次成功[/b]()

  2.     Dim x, rcnt, i
  3.     Dim sql_command As String
  4.     Dim connection_string As String
  5.    
  6.     Worksheets(1).Activate
  7.     rcnt = Range("a1").CurrentRegion.Rows.Count
  8.     i = 1
  9.     For i = 1 To rcnt  
  10.         Set x = CreateObject("adodb.recordset")
  11. '        sql_command = "insert into home(name, age) values('mary','50') "
  12.         aa = "insert into home(name, age) values('" & Cells(i, 1) & "','" & Cells(i, 2) & "' & "')"
  13.    
  14.         sql_command = aa
  15.         connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\joey.sqlite")
  16.         x.Open sql_command, connection_string
  17.         Set x = Nothing
  18.     Next
  19. End Sub
複製代碼
20130806-173.gif

回復 1# joey0415
有試過用 :
Application.EnableEvents = False

Application.EnableEvents = True
包覆處理與顯示期間的程式碼嗎?

TOP

回復 2# luhpro
有呀!不過那不是問題

是sqlite要插入資料時,當然要open與close

每插入一筆就要開關一次,小弟說的是它有一種寫法是

一起打開(begin)

sql (insert XXXXXXX value( XXX,XXX,XXX)
sql (insert XXXXXXX value( XXX,XXX,XXX)
sql (insert XXXXXXX value( XXX,XXX,XXX)
sql (insert XXXXXXX value( XXX,XXX,XXX)

一起提交(commit)

的方式,那種寫法在vba中,我不會寫,所以想請教一下高手指點

謝謝回答

TOP

找到解決的方法一,非正式…

sql語句例如
INSERT INTO "表格名" ("欄位1", "欄位2", ...)VALUES ("值1", "值2", ...);
sqlite它允許
INSERT INTO "表格名" ("欄位1", "欄位2", ...)VALUES ("值1", "值2", ...),("值3", "值4", ...),("值5", "值6", ...);
的方法,一次最多輸入500組數值,超過後一樣會報錯

如果你有1534組要insert進資料庫中,可以分成四組 34  500 500 500 的方法分批加進資料庫中
如果是一組組輸入,上例大概要30秒以上
如果是分四組輸入,估不用2秒

以下是個人程式碼:僅供參考
  1. Sub sqlite批次insert多筆成功()
  2. '最多一次500條,要分段放進去

  3.     Dim x, rcnt, i
  4.     Dim sql_command As String
  5.     Dim connection_string As String
  6.    
  7.     Worksheets(1).Activate
  8.     rcnt = Range("a1").CurrentRegion.Rows.Count
  9.     倍數 = (rcnt \ 500)
  10.     zx = rcnt Mod 500 '找500餘數

  11.      
  12.      j = 0

  13.         If rcnt < 500 Then  '如果小於500時只計算下面資料
  14.         
  15.                         aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
  16.                         Set x = CreateObject("adodb.recordset")
  17.                         cc = ""
  18.                         cc = aa
  19.                         i = 1

  20.                             For i = 1 To rcnt
  21.                                 bb = "('" & Cells(i, 1) & "','" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "')"
  22.                                 '串接要insert的數值對
  23.                                 cc = cc & bb & ","
  24.                             Next
  25.         
  26.                         cc = Left(cc, Len(cc) - 1)  '取字串並刪除最後一個","
  27.                         sql_command = cc
  28.                         connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
  29.                         x.Open sql_command, connection_string
  30.                         Set x = Nothing

  31.         Else '如果大於500時,要算下面兩種資料
  32.         
  33.                         aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
  34.                         Set x = CreateObject("adodb.recordset")
  35.                         cc = ""
  36.                         cc = aa
  37.                         i = 1
  38.                             For i = 1 To zx
  39.         
  40.                                 bb = "('" & Cells(i, 1) & "','" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "')"
  41.                                 cc = cc & bb & ","
  42.                             Next
  43.                         cc = Left(cc, Len(cc) - 1)
  44.                         sql_command = cc
  45.                         connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
  46.                         x.Open sql_command, connection_string
  47.                         Set x = Nothing

  48.                         For j = 0 To 倍數 - 1
  49.         
  50.                         aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
  51.                         Set x = CreateObject("adodb.recordset")
  52.                         cc = ""
  53.                         cc = aa
  54.                         i = 1
  55.         
  56.                             For i = j * 500 + zx + 1 To (j + 1) * 500 + zx
  57.         
  58.                                 bb = "('" & Cells(i + j * 500, 1) & "','" & Cells(i + j * 500, 2) & "','" & Cells(i + j * 500, 3) & "','" & Cells(i + j * 500, 4) & "','" & Cells(i + j * 500, 5) & "','" & Cells(i + j * 500, 6) & "')"
  59.                                 cc = cc & bb & ","
  60.         
  61.                             Next
  62.         
  63.                         cc = Left(cc, Len(cc) - 1)
  64.                         sql_command = cc
  65.                         connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
  66.                         x.Open sql_command, connection_string
  67.                         Set x = Nothing
  68.                     Next
  69.         
  70.         End If

  71. End Sub
複製代碼

TOP

如果大大還有更好的方法,請回文讓小弟多學習,這資料可是花了很久的時間才突破的

TOP

如果大大還有更好的方法,請回文讓小弟多學習,這資料可是花了很久的時間才突破的
joey0415 發表於 2013-8-7 16:10


小弟引用你上述的方式,一直跑到第500筆即失敗
以下是小弟的方法,速度好像還好,範列 4000筆,有比你原來的一筆一筆快,但應該比你的一次500筆慢
  1. Sub SQLite_insert()

  2. Sheets("SQL JOIN").Select
  3. SQLName = "D:\Dropbox\SQLite3\Stock.sqlite"

  4.     insert = "insert into Stock values"
  5.     ret = ""
  6.     ret = insert
  7.    
  8.     et = 113 '導入的標題數
  9.     rcnt = Range("A1").CurrentRegion.Rows.Count '計算所有的行
  10.     xa = 3 '從第三行開始計算

  11.             For j = xa To rcnt - 2
  12.                 zc = ""
  13.                     For i = 1 To et
  14.                         za = Cells(j, i)
  15.                             If za = "" Then '空格轉換
  16.                                 za = 0
  17.                                 
  18.                             ElseIf i = 2 Then '日期轉換
  19.                                 If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
  20.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  21.                                 ElseIf Len(Split(za, "/")(1)) = 1 Then
  22.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  23.                                 ElseIf Len(Split(za, "/")(2)) = 1 Then
  24.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  25.                                 Else
  26.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  27.                                 End If
  28.                                 
  29.                             End If
  30.                         zc = zc & "'" & za & "',"
  31.                     Next i
  32.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  33.                 rez = "(" & zc & ")"
  34.                 ret = ret & rez & ","
  35.             Next j
  36.         ret = Left(ret, Len(ret) - 1)  '刪除最後一個","
  37.         SqlImport = ret
  38.         
  39. Set cn = CreateObject("adodb.connection")
  40.     cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName) '開啟sqlite指定資料庫,路徑與檔案名稱要對
  41.     cn.Execute (SqlImport)
  42.     cn.Close
  43. Set cn = Nothing
  44. End Sub
複製代碼

TOP

回復 6# koshi0413

剛才試了 7983筆,1分鐘
python的 for 好像比較快  哈哈
可是execl 還是有方便計算的地方

TOP

回復 5# joey0415


小弟用EXCEL寫入mysql 和 mssql 以前也遇到相同的問題
但小弟解決的方式不是挺優,您不嫌棄的話可以試試看

用連接符號=";"  
把語法連接起來,EXCUTE的動作等迴圈跑完之後,再給他一次EXCUTE就好了

您的語法主要是慢在數量多,你每一列都打開資料庫連結一次,因此速度慢了
連接在一起就只要打開資料庫一次,一次放入五百筆資料,就非常快了!
  1. Sub WRITE_SQL() '寫入資料
  2. conn_connect'這是呼叫開啟SQL
  3. For i = 1 To 10000
  4.     strSQL = "INSERT INTO customer VALUES ('Bob','SHIT','TWN','SHIT2','SHIT3',2016-01-01," & 100 + i & ")"'每一列的語法
  5.     If i = 1 Then strSQL2 = strSQL Else strSQL2 = strSQL2 & ";" & strSQL'將語法連接
  6. Next
  7. Conn.Execute (strSQL2)'執行SQL
  8. End Sub
複製代碼
PKKO

TOP

回復 8# PKKO

P大
小弟的例子就是用跟您一下的方法
恩   vba跑迴圈是不是比較慢呢?
一次500筆很快
一次7999筆,一分鐘
然後一次性丟入 sqlite3

j大的批次方式,好像就是 7999/500 ,分段的一次性丟入
只是小弟分段丟入都出門,7999/3000 ,也一樣丟不進,
很奇怪,不知道是不是sqlite3反應時間的問題,下次有空, 中間丟個等待好了
不過這樣一來,就跟一次性7999迴圈差不多時間?
要試試~~

TOP

本帖最後由 PKKO 於 2016-12-28 21:48 編輯

回復 9# koshi0413


   excel 不會慢到哪邊去哦
我的都是一秒多就結束了(一秒是因為要開啟和關閉資料庫)
您的速度慢是因為妳用了ZA=CELLS(I,J)

excel要快最基本的要件就是先轉為陣列
例如
RNG=[A1].RESIZE(100,10).VALUE'取出100列*10欄位的資料
接著把妳的cells換成RNG就可以了
excel讀取儲存格是非常慢的速度
讀取陣列是非常快的,放資料的時候也一樣哦,不能一個一個放,要一次放一整個陣列進去哦!

以及上方先放入運算的程式,避免每一個if都要執行一次SPLIT
PKKO

TOP

        靜思自在 : 口說一句好話,如口出蓮花;口說一句壞話如口吐毒蛇。
返回列表 上一主題