B4J Question B4j Connect Access MDB file

[Jones Hone]

#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: ucanaccess-4.0.4.jar
    #AdditionalJar: hsqldb.jar
    #AdditionalJar: jackcess-2.1.11.jar
    #AdditionalJar: commons-logging-1.1.3.jar
    #AdditionalJar: commons-lang-2.6.jar
#End Region

Sub Process_Globals
    Private gSQL As SQL
    Private DBFile As String
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("test")
    MainForm.Show
    '
    DBFile = File.DirApp & "\B4JTEST1.MDB"
    Try
        gSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFile & ";memory=true")
        Dim Cursor As ResultSet
        Cursor = gSQL.ExecQuery("SELECT * from PLU")
        Do While Cursor.NextRow
            Log(Cursor.GetString("PLUNO") & "." & Cursor.GetString("PNAME"))
        Loop
    Catch
        Log(LastException.Message)
    End Try
End Sub

If there is a table named PLU(only 10 records.) in b4jtest1.mdb.
It only takes 2 seconds to execute the above program.

But. If there is a table named PLU(only 10 records) and HIST table(have 100,000 records) in b4jtest1.mdb
It need takes 40 seconds to execute the above program.

Why do I just read the PLU table, if there are other tables in the database, it will slow down?
Is there a solution?
Thank you!

 

[Erel]

The best solution is to convert the database to a proper engine such as SQLite.

Which part is slow? Switch to the async methods. They are simple to use and at least your app will not freeze.

 

[Jones Hone]

The database is being used by the existing program, and conversion to other databases is not allowed.
gSQL.Initialize(): This command took too much time.
Switch to gSQL.InitializeAsync() always slow.

 

[Jones Hone]

I found. When the program is running will auto generate Ucanaccess_net.ucanaccess.jdbc.DBReference@10c2bb3 this temporary directory.
The directory size bigger than the b4jtest1.mdb database(Now the database size is 205MB).

It should be to create this temporary directory, so it took so much time.
Can let it not create a temporary directory?

 

[Erel]

InitializeAsync will not be faster, but at least it will not freeze the app.

Can let it not create a temporary directory?

It is the underlying jdbc driver that does it. You will need to check whether it is configurable: http://ucanaccess.sourceforge.net/site.html

 

[Jones Hone]

look like can't configurable.
Thank you.

 

[BigBoss123]

Just noticed you still have an old *mdb using Jet engine 4.
Can you not update to the new Jet engine 12 and use *.accdb.

Cheers

 

[Jorge M A]

UCanAccess uses an HSQLDB "mirror database" which by default is stored in memory and must be recreated when the application opens the Access database. That involves copying the data from the Access tables into HSQLDB tables, which can take some time if the Access database is large. Having the Access database on a network share will further slow that process.
If the Access database is unlikely to change very often between the times that you launch your Java app then you could use the UCanAccess keepMirror connection parameter to persist the mirror database in a folder on your local hard drive. That would reduce your application startup time because UCanAccess would not have to rebuild the mirror database each time.
See the UCanAccess site for details.

Source: https://stackoverflow.com/questions/31991838/slow-initial-connection-to-ms-access-database

 

[OliverA]

Besides the mirror database, you may want to try not setting memory=false and try to increase the JVM heap space

Links:

Ucanaccess_net.ucanaccess.jdbc.DBReference@ac6b76 folder

I am using UcanAccess(net.ucanaccess.jdbc.UcanaccessDriver) Driver to connect with MS Access Database ,I am able to interact with database but every time i start my server and db is loaded ,a file ...

stackoverflow.com

How to control Java heap size (memory) allocation (xmx, xms) | alvinalexander.com

alvinalexander.com

 

[Jones Hone]

@BigBoss123:
The database is being used by an existing program and cannot be replaced with .accdb

@Jorge MA:
I have tried "keepMirror", But, That database is updated at any time. So it will always rebuild the mirror. So it still took a lot of time.

@OliverA:
I have tried memory=false, But it's always out of memory! Because my database may be nearly 1G MB in size.

It seems that I may have to give up using UCANACCESS.
Excuse me, is there any other library available?