B4J Question UCanAccess with big Access database

Chris2

Active Member
Licensed User
I am using the UCanAccess library to connect to a big Microsoft Access (.mdb) database with jSQL and am finding it to be very slow to initialize.

For example,
B4X:
Private sql As SQL
Private longStart As Long

Public Sub Initialise (dbPath As String)   
    longStart=DateTime.Now
    sql.InitializeAsync("sql", "net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://${dbPath}/BigDB.mdb;memory=false"$, "", "")
End Sub

Sub sql_Ready (Success As Boolean)
    Log(Success)
    If Success = False Then
        Log(LastException)
        Return
    End If
    Dim t As Long = (DateTime.Now-longStart)/1000
    Log("Time taken = " & t)
    sql.Close
End Sub
gives a time taken of 1489s (about 25 minutes) with a 900MB database. This was done in Release mode.
Everything is running locally, with the database local too.

Initially I was getting an out of memory error, but solved this by increasing the heap size with:
B4X:
#VirtualMachineArgs: -Xms4g -Xmx4g
The end purpose here is to build an app to migrate some data from the Access database to an SQL Server or SQLite one, changing a few things along the way. So connecting to the .mdb is necessary.

Is there anything I can do to make the initiailisation quicker, or is this just a limitation of Access &/or UCanAccess?
 

amorosik

Expert
Licensed User
I am using the UCanAccess library to connect to a big Microsoft Access (.mdb) database with jSQL and am finding it to be very slow to initialize.

For example,
B4X:
Private sql As SQL
Private longStart As Long

Public Sub Initialise (dbPath As String)  
    longStart=DateTime.Now
    sql.InitializeAsync("sql", "net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://${dbPath}/BigDB.mdb;memory=false"$, "", "")
End Sub

Sub sql_Ready (Success As Boolean)
    Log(Success)
    If Success = False Then
        Log(LastException)
        Return
    End If
    Dim t As Long = (DateTime.Now-longStart)/1000
    Log("Time taken = " & t)
    sql.Close
End Sub
gives a time taken of 1489s (about 25 minutes) with a 900MB database. This was done in Release mode.
Everything is running locally, with the database local too.

Initially I was getting an out of memory error, but solved this by increasing the heap size with:
B4X:
#VirtualMachineArgs: -Xms4g -Xmx4g
The end purpose here is to build an app to migrate some data from the Access database to an SQL Server or SQLite one, changing a few things along the way. So connecting to the .mdb is necessary.

Is there anything I can do to make the initiailisation quicker, or is this just a limitation of Access &/or UCanAccess?

For 115 Mbyte mdb file, on my pc, your routine is completed about 7 sec
For 1450 Mbyte accdb file, on my pc, your routine after 2 min (cpu 100%), abort execution
Cpu i7, ssd
 
Upvote 0

Chris2

Active Member
Licensed User
Thanks both for your responses.

Yeah, I had seen about the keepMirror setting. Unfortunately the purpose of this app will be to do a one-time conversion, so it will only ever be run once for each .mdb database. So I don't think keepMirror will help in this circumstance.

For 115 Mbyte mdb file, on my pc, your routine is completed about 7 sec
For 1450 Mbyte accdb file, on my pc, your routine after 2 min (cpu 100%), abort execution
Cpu i7, ssd
The laptop I was running this on isn't that quick, an i3-5005u, 8GB RAM, with a newer SSD though.

I think I'll just have to live with it, and hope that when it's used in the field the machine is a shiny new server :).
 
Upvote 0
Top