B4J Question UCanAccess with big Access database

[Chris2]

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,

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:

#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?

 

[Erel]

Check this: https://stackoverflow.com/a/32011512/971547

 

[amorosik]

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,

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:

#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

 

[Chris2]

Thanks both for your responses.

Check this: https://stackoverflow.com/a/32011512/971547

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 .

 

[amorosik]

You can use a converter to Sqlite or other db server system