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();
    }

}

Answer 1

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.

Answer 2

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.