Data Query Takes a while

Mar 18, 2013 at 1:35 AM
Hi

I'm using the code below to get SQLite data into Excel. This works relatively fine for small queries, but large queries (eg 3675 rows and 163 columns) take more than 5 minutes to import. Did I do something wrong writing this code? Thx for any replays.

fileToOpen = sPath & "\ADM_Temp.db3"
'init
InitReturn = SQLite3Initialize
If InitReturn <> SQLITE_INIT_OK Then
    MsgBox "Error Initializing SQLite. Error: " & Err.LastDllError
    Exit Sub
End If

testFile = fileToOpen
RetVal = SQLite3Open(testFile, myDbHandle)


'prepare statement
RetVal = SQLite3PrepareV2(myDbHandle, "SELECT * FROM FinalData", myStmtHandle)
Set oSheet = Sheets("Output")
colnum = 1
rownum = 16
'if statement ok
If RetVal = 0 Then
    RetVal = SQLite3Step(myStmtHandle)
    'get column names
    arCols = GetColNames(myStmtHandle)
    For i = 0 To UBound(arCols)
        oSheet.Cells(rownum, i + colnum).Value = arCols(i)
    Next i
    ColCnt = UBound(arCols)

   RetVal = SQLite3Finalize(myStmtHandle)

     'fill rows from table
RetVal = SQLite3PrepareV2(myDbHandle, "SELECT * FROM FinalData", myStmtHandle)
    While RetVal <> SQLITE_DONE
        RetVal = SQLite3Step(myStmtHandle)
        If RetVal = SQLITE_ROW Then
            rownum = rownum + 1
            arVals = ColValues(myStmtHandle, oSheet, rownum, colnum)
         End If
    Wend
End If

RetVal = SQLite3Finalize(myStmtHandle)
RetVal = SQLite3Close(myDbHandle)
SQLite3Free
Kill (sPath & "\ADM_Temp.db3")
Coordinator
Mar 18, 2013 at 10:18 AM
I'd suggest you separate the extraction of data from the database, and pushing the values into the sheet. My suspicion would be that the line
oSheet.Cells(rownum, i + colnum).Value = arCols(i)
is the slow one. You might try to run it with this commented out, just to check. It seems the second part of your code might be a start towards this.

If so, you are better off populating a Variant array in the VBA with the data, then setting the sheet values in one go.

-Govert
Mar 18, 2013 at 10:36 AM
Hi Govert,
Thx for your reply.
Below are the functions I use, I've forgotten to attach them. I really have the impression that the problem lays in the line
arVals = ColValues(myStmtHandle, oSheet, rownum, colnum)
Do you perhaps have an example on how to populate a Variant array in VBA that puts the data in one go on a sheet?

'
Function GetColNames(ByVal stmtHandle As Long)
Dim colCount As Long
Dim colName As String
Dim i As Long
Dim arrCols() As String
colCount = SQLite3ColumnCount(stmtHandle)
ReDim arrCols(colCount)
For i = 0 To colCount - 1
    colName = SQLite3ColumnName(stmtHandle, i)
    arrCols(i) = colName
Next
GetColNames = arrCols
End Function


Function ColValues(ByVal stmtHandle As Long, pSheet As Excel.Worksheet, rownum As Integer, colnum As Integer)
Dim colCount As Long
Dim colName As String
Dim colType As Long
Dim colTypeName As String
Dim colValue As Variant


Dim i As Long
colCount = SQLite3ColumnCount(stmtHandle)
For i = 0 To colCount - 1
    colName = SQLite3ColumnName(stmtHandle, i)
    colType = SQLite3ColumnType(stmtHandle, i)
    colTypeName = TypeName(colType)
    colValue = SQLite3ColumnText(stmtHandle, i)
    pSheet.Cells(rownum, i + colnum).Value = colValue
Next i
End Function
'
Coordinator
Mar 18, 2013 at 10:51 AM
Edited Mar 18, 2013 at 10:54 AM
Hi,

Sorry - I see the first pass just puts the column names in. From your extra code, the problem is probably inside ColValues where you say:
pSheet.Cells(rownum, i + colnum).Value = colValue
Now, if you had an array with all the data, setting the range value once you have an array is easy. You just call
pSheet.Range(.Sheet.Cells(1, 1), pSheet.Cells(10, 10)).Value = myArray
Getting the array filled in is harder, since you don't immediately know how big to make it. You can search for "ReDim Preserve" to resize the array every time, or make a Collection, and then after you've got all the data you turn it into an array.

-Govert
Jul 18, 2013 at 6:44 PM
Edited Jul 18, 2013 at 7:00 PM
Thank's!!!!!