Slow initial connection to MS access database

Question

I'm using UCanAccess to connect my JavaFX app with the database on the shared drive. The first time I open the app and run some query the initial connection to the database takes around 25 seconds.

I put some timestamps and found that the root cause is the below method, specifically the first try catch block takes 25 seconds to execute. After that, every other time I call this method everything runs within a split of second. Any suggestions on how could this be resolved?

public void openDB(){

    // Load MS access driver class


    try {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        System.out.println("There was an error while connecting to the database");
        e.printStackTrace();
    }


    String databasePath ="jdbc:ucanaccess:////server\\MyDB.accdb";


    try {
        this.connection = DriverManager.getConnection(databasePath, "", "");
        this.connection.setAutoCommit(false);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        this.statement = connection.createStatement();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

Can you clarify: are you saying `Class.forName(...)` takes 25 seconds? — James_D, Aug 13 '15 at 15:06
@James_D Correct, that line of code takes 25 seconds. The other two try catch blocks last a second. — Wizard, Aug 13 '15 at 15:12
Try omitting the `Class.forName()` block entirely. It is often not required unless you are using a rather old JDK. — Gord Thompson, Aug 13 '15 at 15:46
I'm using JDK 1.8. I've ommited the block and same thing happens. — Wizard, Aug 13 '15 at 16:08
I've added a timestamp on the begining of the second block and after this.connection = DriverManager.getConnection() method. Timestamp results are below {SysUpTime = 0:0:0} {Timestamp = Thu Aug 13 18:07:23 CEST 2015} {SysUpTime = 0:0:0} {Timestamp = Thu Aug 13 18:07:40 CEST 2015} — Wizard, Aug 13 '15 at 16:11

score 3 · Accepted Answer · answered Aug 14 '15 at 13:38

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.

Thanks Gord once again. This has decresead the connection time from 25 to just 4 seconds. — Wizard, Aug 20 '15 at 04:35

score 2 · Answer 2 · answered Jan 31 '16 at 19:25

So I'm answering my question after sometime in hopes that this will be useful for someone. Even though the above answer from Gord works fine, I would say this is more suitable for larger databases (see UCanAccess site) and I have experienced problems when multiple users tried to connect to the database e.g. there was an issue with locally stored files.

The root cause of my problem was the folder location on the shared drive/server and the connection was slow because the database backend was stored in sixth subfolder from the root directory. This has to do with server security because the server (only the first time) performs a check for each folder it has to go through. When I moved my folder to the root directory, the connection took about 2 seconds.

Slow initial connection to MS access database

2 Answers2

Linked