缓慢的初始连接到MS访问数据库 [英] Slow initial connection to MS access database

问题描述

我正在使用UCanAccess将我的JavaFX应用程序与共享驱动器上的数据库连接。我第一次打开应用程序并运行一些查询时，与数据库的初始连接大约需要25秒。

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.

我放了一些时间戳，发现根本原因如下方法，特别是第一次尝试catch块需要25秒才能执行。在那之后，每隔一次我调用这个方法，一切都在一秒钟之内运行。关于如何解决这个问题的任何建议？

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

}

推荐答案

UCanAccess使用HSQLDB镜像数据库，默认情况下存储在内存中，必须在应用程序打开Access数据库时重新创建。这涉及将Access表中的数据复制到HSQLDB表中，如果Access数据库很大，这可能需要一些时间。在网络共享上使用Access数据库将进一步减慢该过程。

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.

如果Access数据库在您启动Java应用程序的时间之间不太可能经常更改，那么您可以使用UCanAccess keepMirror 连接参数将镜像数据库保留在本地硬盘驱动器上的文件夹中。这将减少您的应用程序启动时间，因为UCanAccess不必每次都重建镜像数据库。有关详细信息，请参见 UCanAccess网站。

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.

这篇关于缓慢的初始连接到MS访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！