返回列表 上一主題 發帖

[發問] 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

回復 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

        靜思自在 : 時時好心就是時時好日。
返回列表 上一主題