JAVA Query with ACCESS - Slowness

Question

I need to access the ACCESS database, the query is accessing correctly, but I noticed that to have the return of information, the JAVA first reads and all the tables and all the data, is that right? does not have a way to perform the query only what is requested?

DAO class:

// File name

    String filename = "\\\\arquivo.mdb";
    String url = "jdbc:ucanaccess://" + filename.trim();
    // Realiza a conexão com o banco de dados
    String usuario = "";
    String senha = "";
    conexao = DriverManager.getConnection(url, usuario, senha);
    return conexao;

the Consultation:

    PreparedStatement ps = connection.prepareStatement("SELECT * FROM ATENDIMENTO");
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("DATA"));
        System.out.println(rs.getString("CLIENTE"));
    }

Answer 1

Ucanaccess uses a HSQLDB "mirror database" which by default is stored in memory and should be recreated when your application opens the Access database. This involves copying data from Access tables to HSQLDB tables, which can take some time if the Access database is large. Having Access database in a shared network area will make the process even slower.

If there is little chance that the Access database will be changed too often between the times you open your Java application then you can use the Ucanaccess keepMirror connection parameter to persist the mirror database in a folder on your local disk. This would reduce the startup time of your application because Ucanaccess would not have to rebuild the mirror bank each time. See the website of the Ucanaccess for more details.

Another possibility of slowness is when the database is stored in a deep subfolder of a shared drive/server (in the case of original question it was stored in the sixth subfolder from the root). At the time of first access, the server’s security mechanism checks every folder it enters. Moving the folder to the root, the connection gets faster.

---

General tips:

• If you only need the fields DATA and CLIENTE, specify them in the SELECT instead of using *:

  SELECT DATA, CUSTOMER FROM CUSTOMER SERVICE

• If you only need specific records (for example after a certain date), add this to WHERE:

  SELECT DATA, CUSTOMER FROM SERVICE WHERE DATA >= (...)

• If even so the amount of data returned is very large, try to pay it:

  SELECT DATA, CUSTOMER FROM CUSTOMER SERVICE WHERE (...) LIMIT 1, 20