返回列表 上一主題 發帖

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

回復 21# PKKO

謝謝p大指點,下星期出差大陸時,來試跑~~到時在回報

不過您說的修改第二點,大蓋省8秒吧,這樣還是要70秒,個人覺的沒道理@@

TOP

回復 20# koshi0413


  我沒有很深入的看您細節的程式碼
但目前只有看到兩個地方可以稍微快一點,你可以試試看這個部分

第一:za=RNG(J,I)  =>這個部分可以不用,只接把所有的za取代為RNG(J,I)即可=>但這個地方影響不大
第二:您的迴圈之內使用了大量的SPLIT,應該在迴圈開始的時候直接將AR=Split(za, "/")
然後IF AR(0)=1 OR LEN(AR(1))=1 THEN
這樣的話程式就不用每一行都執行一次拆開的動作
以上您試試看
PKKO

TOP

回復 18# PKKO

P大,哎呀呀,抱歉咧
應該又試錯了,一樣80秒@@

改成下面這樣是72秒,所以 if 內日期轉換需 8秒
myCols = 123
myRows = 7923
        Rng = [A3].Resize(myRows, myCols).Value  
            For j = 1 To (myRows - 2)
                zc = ""
                    For i = 1 To myCols
                        za = Rng(j, i)
                        zc = zc & "'" & za & "',"
                    Next i
                zc = Left(zc, Len(zc) - 1)
                rez = "(" & zc & ")"
                ret = ret & rez & ","
                DoEvents
            Next j
  1.         
  2. Application.ScreenUpdating = False
  3. StartTime = Timer
  4.         myCols = Range("A2").CurrentRegion.Columns.Count '計算所有的列
  5.         myRows = Range("A1").CurrentRegion.Rows.Count    '計算所有的行
  6.         Rng = [A3].Resize(myRows, myCols).Value         
  7.             For j = 1 To (myRows - 2)
  8.                 zc = ""
  9.                     For i = 1 To myCols
  10.                         za = Rng(j, i)
  11.                             If za = "" Then '空格轉換
  12.                                 za = 0
  13.                                 
  14.                             ElseIf i = 2 Then '日期轉換
  15.                                 If (Len(Split(za, "/")(1)) = 1) And (Len(Split(za, "/")(2)) = 1) Then
  16.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  17.                                 ElseIf Len(Split(za, "/")(1)) = 1 Then
  18.                                     za = Split(za, "/")(0) & "-0" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  19.                                 ElseIf Len(Split(za, "/")(2)) = 1 Then
  20.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-0" & Split(za, "/")(2)
  21.                                 Else
  22.                                     za = Split(za, "/")(0) & "-" & Split(za, "/")(1) & "-" & Split(za, "/")(2)
  23.                                 End If
  24.                                 
  25.                             End If
  26.                         zc = zc & "'" & za & "',"
  27.                     Next i
  28.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  29.                 rez = "(" & zc & ")"
  30.                 ret = ret & rez & ","
  31.                 DoEvents
  32.             Next j
  33. EndTime = Timer
  34. MsgBox Format(EndTime - StartTime, "00.00") & "秒"
  35. Application.ScreenUpdating = True
複製代碼

TOP

回復 18# PKKO

果然用法錯了@@
謝謝 p大指導,今天一氣之下弄了python來導入了,約10秒
明天再來試vba陣列,非常感謝唷!!!!!!

TOP

回復 17# koshi0413


    用法錯囉,你一樣是讀取了cells的值

rng=[a1].resize(r,c).value'r=列數,C=欄位數量
之後把CELLS的部分取代為rng就可以用陣列了

例如原本要取用cells(2,1) 就變成 rng(2,1)
意思完全相同,但不是取用儲存格而是取用陣列,速度直接大增哦!
PKKO

TOP

回復 13# PKKO

p大,今天試了一下,也不知道對不對
一樣 80秒,好像都是卡在迴圈,是不是小弟用法弄錯啦,
https://dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx  <<但是照這個網頁的範例跑是非常快的(16秒),疑問

如果可以的話,可以指導一下嘛?您說的 za=cells(j,i) 改成陣列,實在是試不出來
  1.         myCols = Range("A2").CurrentRegion.Columns.Count '計算所有的列
  2.         myRows = Range("A1").CurrentRegion.Rows.Count    '計算所有的行
  3.         ReDim myArray(1 To myRows, 1 To myCols)          '動態陣列
  4.             For j = 3 To myRows
  5.                 zc = ""
  6.                     For i = 1 To myCols
  7.                         myArray(j, i) = Cells(j, i)
  8.                         za = myArray(j, i)
  9.                             If za = "" Then '空格轉換
  10.                                 za = 0
  11.                                 
  12.                             ElseIf i = 2 Then '日期轉換
  13.                                 za = Replace(za, "/", "-")

  14.                             End If
  15.                         zc = zc & "'" & za & "',"
  16.                     Next i
  17.                 zc = Left(zc, Len(zc) - 1)  '刪除最後一個","
  18.                 rez = "(" & zc & ")"
  19.                 ret = ret & rez & ","
  20.                 DoEvents
  21.             Next j
複製代碼

TOP

回復 15# koshi0413
找到教學了,謝謝P大指點^_^
https://dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx

TOP

回復 13# PKKO

P大,小弟單獨弄了個迴圈來試,一樣要一分鐘也?
是小弟弄錯了嘛

for i = 1 to 10000
cells(i.2) = i
next i

要如何改成陣列呢?

TOP

回復 13# PKKO

哇~~新年快樂,沒想到跨年夜還回文

非常謝謝唷,小弟先改來試一下,如果還慢,在上來請教~~~

所以 cells(i,j) 速度 > Range("A" & i)   這點小弟倒是不知道,還以為一樣呢

TOP

        靜思自在 : 人的眼睛長在前面,只看到別人的缺點,絲毫看不到自己的缺點。
返回列表 上一主題