problems with frames and speed

[Jaume]

Hi everyone,
I'm having problems with UcanAccess,
I'm doing an update to the database and the program is really slow.
Does anybody know how I can improve the speed ?
I Tried to change " memory false to memory true",
I also tried to init with autoCommiat as false, but finally when I did the "commit" took the same time.
I'm working with a database with more than 1.000.000 than registres, could be normal this performance ?
This is my code

---

   boolean ok = false;
    try {
        //con.setAutoCommit(false);
        Properties p = new Properties();
        p.setProperty("singleconnection", "true");
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        con = DriverManager.getConnection("jdbc:ucanaccess://database.mdb;memory=true", p);
        ok = true;

    } catch (Exception a) {
        JOptionPane.showMessageDialog(null, "Error con la conexión de la base de datos: " + a);
    }
    text.setText("Conectado");
    return ok;

---

Anyway, I've another problem.
When I started this update I've created a Frame with a typical message "keep a moment" but this frame just creates the shape, when the update finished then the frame finished.

If anybody could help me.

Thanks a lot !

[Marco Amadei]

Memory=true is the default. It's impossible that you get the same time with
con.setAutoCommit(false); the difference is about 90-95% of the execution time.
use it before the massive update statement; after that commit. Please send your findings (the time now and after the .setAutoCommit(false)) along the code that execute the update.

[Jaume]

Hi,
The first of all, thanks for your answers.
I made a test with the two options and using a smaller database and your are right. with AutoCommit(false) takes less time, about a 33% less.
but it continue to be too slow, especially when I execute the commit to save the registers .

---

public boolean conectar() {
boolean ok = false;
try {
//con.setAutoCommit(false);
Properties p = new Properties();
p.setProperty("singleconnection", "true");
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
con = DriverManager.getConnection("jdbc:ucanaccess://database.mdb;memory=true", p);
ok = true;

    } catch (Exception a) {
        JOptionPane.showMessageDialog(null, "Error con la conexión de la base de datos: " + a);
    }
    text.setText("Conectado");
    return ok;
}

 public void maxminScontrini() throws SQLException{
    int scontrino = 0;
    st = con.createStatement();
  Funciones.MostraHora();
     //  con.setAutoCommit(false);
        String sentence = "select id from scontrini where data_ora >= #" + desde +"  00:00:00# and  data_ora <= #" + hasta +" 23:59:59#";
        System.out.println(sentence);

        ResultSet s = st.executeQuery(sentence);

        while (s.next() == true){
            updateScontrini(s.getInt("id"));
            scontrino ++;

        }
        s.next();

    //con.commit();
   System.out.println("Numeros modificados: " + scontrino);
  Funciones.MostraHora();
 }

 public void updateScontrini(int scontrino) throws SQLException{
     double precio;
     double sconto;
     double precioFinal;
     double scontoFinal;
     double totalScontrino = 0;
     double totalDescuentoScontrino = 0;

     String sentence1 = "select id, prodotto_prezzo_venduto, sconto from movimenti where numero_scontrino = '" + scontrino + "';";
    st = con.createStatement();

         System.out.println("Scontrino" + scontrino);

        ResultSet s = st.executeQuery(sentence1);
        while (s.next() == true){
          int tempId  = s.getInt("id");
          precio = s.getDouble(("prodotto_prezzo_venduto")); // Precio Original
          sconto = s.getDouble("sconto"); //Descuento original
          precioFinal = precio - Funciones.descuento(precio, Integer.valueOf(descuento)); // precio con descuento
          scontoFinal = Funciones.descuento(precio, Integer.valueOf(descuento)); // Descuento aplicado
          totalScontrino += precioFinal;
          totalDescuentoScontrino += scontoFinal;
          String sentence2 = "update movimenti set prodotto_prezzo_venduto ='" + precioFinal+ "', sconto = '" + scontoFinal + "'  where id = '" +tempId + "'";
          st = con.createStatement();
          st.executeUpdate(sentence2);
        }
        String sentence3 = "update scontrini set totale = '"+ totalScontrino +"', sconto = '"+ totalDescuentoScontrino +"' where id = '" +String.valueOf(scontrino) + "'";
        st = con.createStatement();
        st.executeUpdate(sentence3);
// con.commit();

 }

---

This is my little code, maybe I'm doing any error because I'm learning java yet ... :-/

[Gord Thompson]

Also, bear in mind that UCanAccess needs to copy the data into the HSQLDB backing database when it establishes the connection to the Access database file. That process will take some time for a large database. The default memory=true setting helps get that done the fastest.

If you find that the initial connection time is the major contributor to the overall "slowness" you are experiencing then you might consider

• using the keepMirror option in UCanAccess to persist a copy of the HSQLDB backing database, if appropriate for your particular usage patterns, or

• using the Jackcess API directly, since it operates on the Access database file itself and does not need to copy the data into another format before starting to work with it.

[Jaume]

Do you have more information about how I can do that?

Thanks a lot for your answer

[Gord Thompson]

You can get more information from the documentation for UCanAccess and Jackcess, respectively. If you have a specific question then please ask it in a new topic here, or as a question on Stack Overflow.

[Jaume]

Anyway, there is also the problem with the frame, I create a frame before to start
with a typical message "waiting" but this only load the basic structure until Ucanaccess haven't finished it doesn't finish to show the frame
then suddenly java shows the frame correctly,
Has it any relation with Ucanaccess or am I doing something bad with java?

[Gord Thompson]

It sounds like you are doing all of your work in the same thread and the database manipulations are blocking the UI updates. You might want to run the database operations in a separate thread. Look here for more information on how that can be done.

[Marco Amadei]

You're getting something like 100000 rows, and for each one you're executing a query, and of each record of this query you do an update. Am I right? Issue isn't mainly in I/O. An optimisation is likely achievable(with one-shot query) but you should add the custom descuento function to those supported and implemented by ucanaccess(take a look to the site about).
This approach would require both skills and effort.

[Jaume]

Many thanks for you answer!
The programs is working !