Results 1 to 11 of 11

Thread: [RESOLVED] Exporting some data from Sqlite database to excel file takes very long time

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    945

    Resolved [RESOLVED] Exporting some data from Sqlite database to excel file takes very long time

    Hello VbForums again
    I'm trying to retreive some data from Sqlite database and send it to .xls file on OneDrive but it takes about 6 minutes to finish the process..
    The table from which I try to retrieve data has more than 30000 records.
    This is my code:

    Code:
    Dim exl As Object
    Dim wbk As Object
    Dim sht As Object
    Dim rng As Object
    Dim I%, j%
    Dim sPathToOneDrive As String
    sPathToOneDrive = Environ("OneDrive") & "\Data.xls"
    Dim oXLSheet As Object
      Set exl = CreateObject("Excel.Application")  'Create a new instance of Excel
      If Dir(sPathToOneDrive) = "" Then
        Set wbk = exl.Workbooks.Add  'File doesnt exist - add a new workbook
      Else
        Set wbk = exl.Workbooks.Open(sPathToOneDrive) 'File exists - load it
      End If
      Set oXLSheet = wbk.Worksheets(1)
    
        Set sht = wbk.ActiveSheet
        Set rng = sht.Range("A1:Z1000")
    
        For I = 1 To wbk.Worksheets.Count
            Set sht = wbk.Worksheets(I)
    '        If Not sht.Name = "Sheet1" Then sht.Visible = False
        Next I
        StrSql = "select Fname, Lname,  BirthDate, Tel, adr, obs, " & _
        " weight, height, DDR,TPA, Date_visit " & _
        " FROM Maintbl " & _
        " left join Trans_Tbl on Maintbl .Id = Trans_Tbl .PID " & _
        " left join DDR_tbl on Maintbl .Id = DDR_tbl.PID "
    Set Rs = Cnn.OpenRecordset(StrSql)
        
        For I = 0 To Rs.Fields.Count - 1
            
      rng.Cells(1, I + 1).Font.Bold = True
      rng.Cells(1, I + 1).Font.color = vbBlue
        Next I
     
        I = 2
        Do While Not Rs.EOF = True
            For j = 0 To Rs.Fields.Count - 1
                rng.Cells(I, j + 1).Value = Trim(Rs.Fields(j).Value)
            Next j
            Rs.MoveNext
            I = I + 1
        Loop
        
        exl.DisplayAlerts = False
        exl.Visible = True
    
       wbk.SaveAs sPathToOneDrive
    
        Set rng = Nothing
        Set sht = Nothing
        Set wbk = Nothing
        Set exl = Nothing
    Is there a way to speed the process?
    thank you
    Last edited by newbie2; Jun 21st, 2024 at 04:32 AM.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,315

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Without looking at your code, I’ll offer this, however. Put timestamps after (and/in) each function and see where the large amount of processing time is occurring.
    Sam I am (as well as Confused at times).

  3. #3
    Hyperactive Member
    Join Date
    Jan 2018
    Posts
    286

    Re: Exporting some data from Sqlite database to excel file takes very long time

    I don't see anything obvious - you're processing hundreds of rows per second, but you want to process 1000s...

    Is it any faster if you save to a local file rather than directly to the OneDrive folder? (I'm guessing not)

    Can you declare exact variable types for Rs and rng so it doesn't have to do as much late binding?

    Another approach would be to save your query to CSV output and then pull the whole file into Excel at once.

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,330

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Quote Originally Posted by newbie2 View Post
    I'm trying to retreive some data from Sqlite database and send it to .xls file on OneDrive but it takes about 6 minutes to finish the process..
    The table from which I try to retrieve data has more than 30000 records.
    I'm quite sure, that he most amount of time is spent in this code-snippet of yours:

    Code:
        I = 2
        Do While Not Rs.EOF = True
            For j = 0 To Rs.Fields.Count - 1
                rng.Cells(I, j + 1).Value = Trim(Rs.Fields(j).Value)
            Next j
            Rs.MoveNext
            I = I + 1
        Loop
    The whole snippet above can be replaced with a much faster "OneLiner", which fills an entire XL-Cell-Range with just one call:
    Code:
    
      'Rs.GetRows below returns a transposed Variant-Array (beamed into the sheet from TopLeft-Cell "A2")
      ArrFillFromTopLeft sht.Range("A2"), Rs.GetRows(, , , True)
    For the above OneLiner to work, you will need to put the following 3 little Helper-Routines
    either into your Form, but better into a globally reachable *.bas Module
    Code:
    Public Sub ArrFillFromTopLeft(xlCellRange As Object, Arr) 'fills an entire Array from a single TopLeft-Cell-Range
      xlCellRange.Resize(ArrRowCount(Arr), ArrColCount(Arr)).Value = Arr
    End Sub
    
    Public Function ArrRowCount(Arr) As Long
      ArrRowCount = UBound(Arr, 1) - LBound(Arr, 1) + 1
    End Function
    
    Public Function ArrColCount(Arr) As Long
      ArrColCount = UBound(Arr, 2) - LBound(Arr, 2) + 1
    End Function
    Your code should now run in seconds, instead of minutes.

    As for the Trim(...) you've applied against every value in your Rs... (in your former copy-loop) -
    please apply it at SQL-Query-Level (and only for the Fields which are of type Text, like e.g. Trim(FName), Trim(LName),...).

    Olaf

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    945

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Thank you Olaf for the code snippet
    Your code should now run in seconds, instead of minutes.
    When I read this, i felt very happy for a while

    I copied the three Helper-Routines in a Module and run the code.

    Code:
    Dim exl As Object
    Dim wbk As Object
    Dim sht As Object
    Dim rng As Object
    Dim I%, j%
    Dim sPathToOneDrive As String
    sPathToOneDrive = Environ("OneDrive") & "\Data.xls"
    Dim oXLSheet As Object
      Set exl = CreateObject("Excel.Application")  'Create a new instance of Excel
      If Dir(sPathToOneDrive) = "" Then
        Set wbk = exl.Workbooks.Add  'File doesnt exist - add a new workbook
      Else
        Set wbk = exl.Workbooks.Open(sPathToOneDrive) 'File exists - load it
      End If
      Set oXLSheet = wbk.Worksheets(1)
    
        Set sht = wbk.ActiveSheet
        Set rng = sht.Range("A1:Z1000")
    
        For I = 1 To wbk.Worksheets.Count
            Set sht = wbk.Worksheets(I)
    '        If Not sht.Name = "Sheet1" Then sht.Visible = False
        Next I
        StrSql = "select Fname, Lname,  BirthDate, Tel, adr, obs, " & _
        " weight, height, DDR,TPA, Date_visit " & _
        " FROM Maintbl " & _
        " left join Trans_Tbl on Maintbl.Id = Trans_Tbl .PID " & _
        " left join DDR_tbl on Maintbl.Id = DDR_tbl.PID "
    Set Rs = Cnn.OpenRecordset(StrSql)
        
        For I = 0 To Rs.Fields.Count - 1
            
      rng.Cells(1, I + 1).Font.Bold = True
      rng.Cells(1, I + 1).Font.color = vbBlue
        Next I
     
       ArrFillFromTopLeft sht.Range("A2"), Rs.GetRows(, , , True)
        
        exl.DisplayAlerts = False
        exl.Visible = True
    
       wbk.SaveAs sPathToOneDrive
    
        Set rng = Nothing
        Set sht = Nothing
        Set wbk = Nothing
        Set exl = Nothing
    I get:Runtime error 1004
    Application defined or object defined error
    here:
    xlCellRange.Resize(ArrRowCount(Arr), ArrColCount(Arr)).Value = Arr
    thank you

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,330

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Quote Originally Posted by newbie2 View Post
    I get:Runtime error 1004
    Application defined or object defined error
    here:
    xlCellRange.Resize(ArrRowCount(Arr), ArrColCount(Arr)).Value = Arr
    Is this a very, very old Excel-Version?
    (because the Resize-Method should be available for decades now)

    Just played that through with my (also relatively old) Excel 2016 -
    and the following Demo-Code works without any Problems (in a Project with an RC6-reference):

    Code:
    Option Explicit
    
    Private Sub Form_Load()
      Dim i As Long, j As Long, Rs As cRecordset
     
      Dim exl As Object, wbk As Object, sht As Object
      Set exl = CreateObject("Excel.Application")  'Create a new instance of Excel
      Set wbk = exl.Workbooks.Add 'File exists - load it
      Set sht = wbk.ActiveSheet
     
      With New_c.MemDB
        .Exec "Create Table T(ID Integer Primary Key, LName Text, FName Text, Dat ShortDate)"
        For i = 1 To 30000 'insert 30000 demo-records into table T
          .ExecCmd "Insert Into T Values(?,?,?,?)", i, "LName " & i, "FName " & i, Date + i
        Next
        Set Rs = .GetRs("Select * From T")
      End With
      
      For i = 0 To Rs.Fields.Count - 1 'write a Header-Row to Excel (formatted in Blue)
        sht.cells(1, i + 1).Font.Bold = True
        sht.cells(1, i + 1).Font.Color = vbBlue
        sht.cells(1, i + 1).Value = Rs.Fields(i).Name
      Next i
     
      ArrFillFromTopLeft sht.Range("A2"), Rs.GetRows(, , , True) 'write the Rs-Data "in one go"
        
      exl.DisplayAlerts = False
      wbk.SaveAs "c:\temp\Export_30000.xlsx"
      exl.quit
    End Sub
    
    Public Sub ArrFillFromTopLeft(xlCellRange As Object, Arr) 'fills an entire Array from a single TopLeft-Cell-Range
      xlCellRange.Resize(ArrRowCount(Arr), ArrColCount(Arr)).Value = Arr
    End Sub
    
    Public Function ArrRowCount(Arr) As Long
      ArrRowCount = UBound(Arr, 1) - LBound(Arr, 1) + 1
    End Function
    
    Public Function ArrColCount(Arr) As Long
      ArrColCount = UBound(Arr, 2) - LBound(Arr, 2) + 1
    End Function
    The above will produce an "Export_30000.xlsx" file in "C:\temp",
    which contains a blue formatted Header - followed by 30000 records.
    (all done in about 1-2 seconds).

    Please check, whether the above (and complete) Demo-Snippet works on your machine.

    Olaf

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    945

    Re: Exporting some data from Sqlite database to excel file takes very long time

    thank you very much Olaf
    I playd with your demo and it isworking like a charm
    But I'm still having trouble with my code.
    in my project I 'm making reference to Microsoft excel 12.0
    I thought perhaps this is the cause
    So I added Microsoft excel 12.0 to your demo but it still works.

    Then instead of using my table , i created a temprory table as in your demo and it worked.
    I wonder why it is working with the temporary table and not with real tables
    Last edited by newbie2; Jun 21st, 2024 at 07:12 AM.

  8. #8
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,757

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Probably the issue is with your SQL statement, quite a few left joins in there. You should make sure it returns what you expect it to return.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    945

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Quote Originally Posted by VanGoghGaming View Post
    Probably the issue is with your SQL statement, quite a few left joins in there. You should make sure it returns what you expect it to return.
    I modified the SQL statement and dropped the inners and reduced it to :

    select Fname, Lname FROM Maintbl
    but still having error

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,330

    Re: Exporting some data from Sqlite database to excel file takes very long time

    Quote Originally Posted by newbie2 View Post
    I modified the SQL statement and dropped the inners and reduced it to :



    but still having error
    As long as the returned Rs does have:
    - an Rs.Fields.Count > 0
    - as well as an Rs.RecordCount > 0

    Then the "one-liner" (where you pass a VariantArray along via Rs.GetRows(...)) - should work.

    Can you not set a breakpoint, then stepping through it with F8 (Step-By-Step, checking variables and Objects in the Immediate-Window),
    to find where exactly "things go wrong"?

    Olaf

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    945

    Re: Exporting some data from Sqlite database to excel file takes very long time

    I'm so grateful to you Olaf
    It is a mistake in the SQL statement as VanGoghGaming pointed out.
    Sorry for my carelessness
    solved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width