JAVA Query with ACCESS - Slowdown

Question

JAVA Query with ACCESS - Slowdown

Navigation

#1 by (2 votes)

0

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

DAO Class:

// Filename

    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;

a Query:

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

java netbeans ms-access

asked by anonymous 15.02.2016 / 14:33

1 answer

phpMailer causes error syntax error, unexpected T_FUNCTION when hosted [closed] Error unexpected end of file in cakephp

score 2 · Accepted Answer

UCanAccess uses a HSQLDB "mirror database" which by default is stored in memory and must be re-created when your application opens the Access database. This involves copying the data from the Access tables to the HSQLDB tables, which can take some time if the Access database is large. Having the Access database in a shared network area will slow the process down.

If there is little chance that the Access database will change very often between the times you open your Java application then you can use the UCanAccess keepMirror connection parameter to persist the mirror bank 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 UCanAccess site 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 SELECT instead of * :

SELECT DATA, CUSTOMER FROM ATTENDANCE
If you just need specific records (for example after a certain date), add this to WHERE :

SELECT DATA, CUSTOMER FROM CUSTOMER WHERE DATA > = (...)
If even the amount of data returned is too large, try paging it:

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