sqlite jdbc

[sottovento]

Originariamente inviato da VICIUS

In questo modo però se step() o la commit lanciano una eccezione la dispose() non è mai invocata. Dovresti metterla in una finally {}. Trattandosi di una libreria jni meglio non rischiare.

Certamente! Si vede che ho scritto una cosa a forza di tentativi

[sottovento]

Fatto il test jdbc driver VS sqlite4java wrapper.
In sintesi, il risultato:

Test with jdbc driver...
Total time: 21.997608
Test with wrapper...
Total time: 7.7242694

Apparentemente il vincitore e' il wrapper.
Non mi sento di affermarlo con certezza a causa della mancanza di documentazione. Sono quindi andato per tentativi e non posso escludere che manchi qualcosa che una volta aggiunto possa peggiorare i tempi. Cmq i dati ci sono, nel database.

Ho fatto il test in questo modo:
- per quanto riguarda il jdbc driver, ho preso il codice di wingman e l'ho modificato leggermente (vale a dire, non mi interessava il tempo di creazione delle tabelle, ...).
Inoltre, wingman aveva portato i cicli di set della prepared statement FUORI dal ciclo principale, visto che i dati che si scrivevano erano sempre gli stessi.
Purtroppo questo non e' possibile con il wrapper; dunque, per uniformita' di vedute (e poi, anche se i dati sono sempre gli stessi, vogliamo far finta che non lo siano) ho riportato il set della prepared nel ciclo stesso.

- per quanto riguarda il wrapper, ho fatto riferimento a quanto ho scritto precedentemente, cercando di portarlo nella maniera piu' simile possibile a quanto fatto da wingman.
E' da notare che, nonostante la banalita' del programma, ho dovuto lottare per un bel po' contro eccezioni di vario tipo poiche' qualcosa mancava sempre o era fuori posto, ecc.
Inoltre in un caso (una volta che credevo che ormai fosse tutto a posto) ho avuto un crash della jvm.

Quindi, il driver jdbc e' piu' lento ma piu' sicuro. Una cosa su cui meditare...


Qui l'applicazione che ho usato. Mi vergogno un po' vista la bassa qualita', trattasi di un'applicazione da pausa pranzo:

package sqliteperftest;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteStatement;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

/**
 *
 * @author MEGI
 */
public class SqlitePerfTest
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) 
    {
        try 
        {
            System.out.println ("Test with jdbc driver...");
            TestSqliteDriver.doit();
            System.out.println ("Test with wrapper...");
            TestSqliteWrapper.doit();
        }
        catch (Exception ex) 
        {
            System.out.println ("" + ex);
        }
    }
}



class TestSqliteDriver
{
  public static int NUM_TEXT = 100;
  public static int NUM_INTEGER = 100;
  public static int NUM_INSERT = 100000;

  public static void doit() throws ClassNotFoundException
  {
    
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:sampledriver.db");
      
      connection.setAutoCommit(false);
      
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists two_hundred_one");
      String create = "create table two_hundred_one (id integer";
      for(int i=1; i<=NUM_TEXT; i++)
      {
          create += ", text_" + i + " text";
      }
      for(int i=1; i<=NUM_INTEGER; i++)
      {
          create += ", int_" + i + " integer";
      }
      create += ")";
      statement.executeUpdate(create);
      
      String insert = "insert into two_hundred_one values(?";
      for(int i=0; i<NUM_TEXT + NUM_INTEGER; i++)
      {
          insert += ", ?";
      }
      insert += ")";
      
      long start = System.nanoTime();
      PreparedStatement preparedStatement = connection.prepareStatement(insert);
      
      for(int i=1; i<=NUM_INSERT; i++)
      {
        preparedStatement.setInt(1, i);
        for(int j=1; j<=NUM_TEXT; j++)
        {
          preparedStatement.setString(j + 1, "Text_" + j);
        }
        for(int j=1; j<=NUM_INTEGER; j++)
        {
          preparedStatement.setInt(NUM_TEXT + j + 1, j);
        }
        preparedStatement.executeUpdate();
      }
      
      connection.commit();
      long end = System.nanoTime();
      System.out.println("Total time: " + (float)(end-start)/1000000000 );
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
      e.printStackTrace(System.out);
    }
    finally
    {
      try
      {
        if(connection != null)
        {
          connection.close();
        }
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    
  }
}

class TestSqliteWrapper
{
    public static void doit()
    {
        String              query;
        SQLiteConnection    db                  = null;
        SQLiteStatement     st                  = null;

        try
        {
            /* Open connection */
            db              = new SQLiteConnection(new File("samplewrapper.db"));
            db.open(true);

            SQLiteStatement stmtDrop = null;
            try
            {
                db.exec("BEGIN");
                query           = "drop table if exists two_hundred_one";
                stmtDrop = db.prepare(query);
                stmtDrop.step();
                db.exec("COMMIT");
            }
            catch (Exception ee)
            {
                //System.out.println (ee.toString());
                db.exec("ROLLBACK");
            }
            finally
            {
                if (stmtDrop != null)
                {
                    stmtDrop.dispose();
                }
            }
            
            db.exec("BEGIN");
            String create = "create table two_hundred_one (recid integer";

            for(int i=1; i<=TestSqliteDriver.NUM_TEXT; i++)
            {
                create += ", text_" + i + " text";
            }
            for(int i=1; i<=TestSqliteDriver.NUM_INTEGER; i++)
            {
                create += ", int_" + i + " integer";
            }
            create += ")";
            db.exec(create);
            db.exec("COMMIT");
            
            db.exec("BEGIN");
            query = "insert into two_hundred_one (recid";
            for(int i=1; i<= TestSqliteDriver.NUM_TEXT; i++)
            {
                query += ", text_" + i;
            }
            for(int i=1; i<=TestSqliteDriver.NUM_INTEGER; i++)
            {
                query += ", int_" + i;
            }
            
            query += ") values(?";

            for(int i=0; i < TestSqliteDriver.NUM_TEXT + TestSqliteDriver.NUM_INTEGER; i++)
            {
                query += ", ?";
            }
            query += ")";

            long start = System.nanoTime();
            st  = db.prepare(query);
            for(int i=1; i<=TestSqliteDriver.NUM_INSERT; i++)
            {
                for(int j=1; j<=TestSqliteDriver.NUM_TEXT; j++)
                {
                    st.bind(j+1, "Text_" + j);
                }

                for(int j=1; j<=TestSqliteDriver.NUM_INTEGER; j++)
                {
                    st.bind(TestSqliteDriver.NUM_TEXT + j + 1, j);
                }

                st.bind(1, i);
                st.step();
                st.reset();
            }
            db.exec("COMMIT");
            long end = System.nanoTime();
            System.out.println("Total time: " + (float)(end-start)/1000000000 );
        }
        catch (Exception ee)
        {
            System.out.println ("Exception inserting record: " + ee);
            ee.printStackTrace(System.err);       // test
            System.exit(0);                      // test
        }
        finally
        {
            try
            {
                if (db != null)
                {
                    db.dispose();
                }
            }
            catch (Exception e) { }
            try
            {
                if (st != null)
                {
                    st.dispose();
                }
            }
            catch (Exception e) {}
        }
    }
}

[Vincenzo1968]

[Vincenzo1968]

http://code.google.com/p/sqlite4java/

sqlite4java is not a JDBC driver. Access to the database is made through the custom interfaces of com.almworks.sqlite4java package. Tighter integration with SQLite offers better performance and features not available through JDBC interfaces.



http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

[Vincenzo1968]

Originariamente inviato da sottovento

Si, la documentazione ufficiale e' carentissima e lavorare con sqlite4java e' un mal di testa.
Io ho proceduto per tentativi, facendo delle supposizioni e non toccando piu' nulla quando ho ottenuto quello che mi serve.
Ovviamente il lavoro andrebbe approfondito, ma preferisco fare altro, nella mia vita

Collegamento: l'esempio che hai riportato funziona bene:

            db              = new SQLiteConnection(new File(sqliteFile));
            db.open(true);

Javadoc ovviamente assente, cmq e' chiaro.

Nel tuo esempio, c'era una query ma non una insertion. Ecco qui.
Per prima cosa ho cancellato la tabella, cosi' da ricrearla (i miei campi possono cambiare a seconda del macchinario da cui ho prelevato i dati):

            try
            {
                db.exec("BEGIN");
                query           = "DROP TABLE " + tableName;
                SQLiteStatement stmtDrop = db.prepare(query);
                stmtDrop.step();
                db.exec("COMMIT");
                stmtDrop.dispose();
            }
            catch (Exception ee)
            {
                //System.out.println (ee.toString());
                db.exec("ROLLBACK");
            }

Esiste anche la DROP TABLE IF EXISTS ma al momento della stesura del codice c'erano ancora troppe variabili in gioco e non ho provato.
Da notare:
- aprire la transazione con db.exec("BEGIN");
-creare lo statement ed eseguirlo;
- chiudere la transazione con db.exec("COMMIT");
- chiudere la transazione con db.exec("ROLLBACK") in caso di errore;
- dispose dello statement.

Esiste anche l'autocommit ed immagino esistano anche altre opzioni che probabilmente evitano questi passaggi. Non le ho provate ne' cercate.

Per creare la tabella, ho provato un altro stile, ma non so dire quale sia meglio:

            db.exec("BEGIN");
            String query           = buildCreateTableQuery(tableName, model);
            db.exec(query);
            db.exec("COMMIT");

la buildCreateTableQuery() e' triviale e restituisce la stringa. Non la riporto qui per ovvii motivi.
Anche qui occorre prestare attenzione a fare il ROLLBACK nel caso l'operazione fallisca; altrimenti tutte le altre operazioni continueranno a fallire.

Per l'inserimento dei dati ho usato una preparedStatement:

            String query           = buildInsertQuery(tableName, model);
            SQLiteStatement st              = db.prepare(query);

Analogamente, buildInsertQuery() e' triviale.

In accordo con il mio software, ho poi effettuato il binding per ogni record letto da file, ed eseguito l'operazione:

for (int i = 0; i < l; i++)
{
    String strType  = model.getValueAt(i, 3).toString();
    switch (strType) 
    {
        case "INT":
            st.bind(i+1, Integer.parseInt(vect[i]));
            break;
        case "DOUBLE":
            st.bind(i+1, Double.parseDouble(vect[i]));
            break;
        default:
            st.bind(i+1, vect[i]);
            break;
    }
}
st.step();
countSuccess++;
st.reset(true);

st.step() funziona anche nel caso delle query di update.

Non conoscendo nulla riguardo l'area di commit (grandezza, prestazioni,....) ho deciso di fare BEGIN...COMMIT/ROLLBACK ogni 100 record. Non ho voluto indagare di piu' per mancanza di tempo.

Da notare st.reset() che effettua l'unbinding in modo che al ciclo successivo possa fare il binding con i nuovi valori.
Penso che questo reset possa essere evitato in casi si usi un reference (in tal caso immagino basti aggiornare l'oggetto referenziato), ma e' solo un'ipotesi

Perché non proponi di aggiungere questa tua miniguida nel sito ufficiale?

[sottovento]

Originariamente inviato da Vincenzo1968

Perché non proponi di aggiungere questa tua miniguida nel sito ufficiale?

No dai, e' solo un test, e non sono nemmeno sicuro che sia 100% corretto! Per esempio, manca la chiusura del database alla fine. Non e' un codice di qualita', e' solo un esempio per voi, cari amici

[Vincenzo1968]

Si beh, quando hai cinque minuti di tempo puoi aggiustarlo un attimino e postarlo su google code. Secondo me è molto meglio del "getting started" ufficiale.



Comunque, dico, posta non il confronto con jdbc, ma questo: http://www.hwupgrade.it/forum/showpo...3&postcount=35

[Vincenzo1968]

vincenzo@mar mar 05$ javac -cp '.:sqlite-jdbc-3.7.2.jar:sqlite4java.jar:h2-1.3.170.jar' SqlitePerfTest.java
vincenzo@mar mar 05$ java -cp '.:sqlite-jdbc-3.7.2.jar:sqlite4java.jar:h2-1.3.170.jar' SqlitePerfTest
Test with jdbc driver...
Total time: 21.36797
Test with wrapper...
Total time: 7.377926
Test with h2 driver...
Total time: 28.130777



H2

//package sqliteperftest;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteStatement;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.*;

/**
 *
 * @author MEGI
 */
public class SqlitePerfTest
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) 
    {
        try 
        {
            System.out.println ("Test with jdbc driver...");
            TestSqliteDriver.doit();
            System.out.println ("Test with wrapper...");
            TestSqliteWrapper.doit();
            System.out.println ("Test with h2 driver...");
            TestH2Driver.doit();            
        }
        catch (Exception ex) 
        {
            System.out.println ("" + ex);
        }
    }
}

class TestH2Driver
{
  public static int NUM_TEXT = 100;
  public static int NUM_INTEGER = 100;
  public static int NUM_INSERT = 100000;

  public static void doit() throws ClassNotFoundException
  {
    
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.h2.Driver");

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:h2:sampleh2driver.db", "sa", "");
      
      connection.setAutoCommit(false);
      
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists two_hundred_one");
      String create = "create table two_hundred_one (id integer";
      for(int i=1; i<=NUM_TEXT; i++)
      {
          create += ", text_" + i + " text";
      }
      for(int i=1; i<=NUM_INTEGER; i++)
      {
          create += ", int_" + i + " integer";
      }
      create += ")";
      statement.executeUpdate(create);
      
      String insert = "insert into two_hundred_one values(?";
      for(int i=0; i<NUM_TEXT + NUM_INTEGER; i++)
      {
          insert += ", ?";
      }
      insert += ")";
      
      long start = System.nanoTime();
      PreparedStatement preparedStatement = connection.prepareStatement(insert);
      
      for(int i=1; i<=NUM_INSERT; i++)
      {
        preparedStatement.setInt(1, i);
        for(int j=1; j<=NUM_TEXT; j++)
        {
          preparedStatement.setString(j + 1, "Text_" + j);
        }
        for(int j=1; j<=NUM_INTEGER; j++)
        {
          preparedStatement.setInt(NUM_TEXT + j + 1, j);
        }
        preparedStatement.executeUpdate();
      }
      
      connection.commit();
      long end = System.nanoTime();
      System.out.println("Total time: " + (float)(end-start)/1000000000 );
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
      e.printStackTrace(System.out);
    }
    finally
    {
      try
      {
        if(connection != null)
        {
          connection.close();
        }
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }    
  }
}



class TestSqliteDriver
{
  public static int NUM_TEXT = 100;
  public static int NUM_INTEGER = 100;
  public static int NUM_INSERT = 100000;

  public static void doit() throws ClassNotFoundException
  {
    
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:sampledriver.db");
      
      connection.setAutoCommit(false);
      
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists two_hundred_one");
      String create = "create table two_hundred_one (id integer";
      for(int i=1; i<=NUM_TEXT; i++)
      {
          create += ", text_" + i + " text";
      }
      for(int i=1; i<=NUM_INTEGER; i++)
      {
          create += ", int_" + i + " integer";
      }
      create += ")";
      statement.executeUpdate(create);
      
      String insert = "insert into two_hundred_one values(?";
      for(int i=0; i<NUM_TEXT + NUM_INTEGER; i++)
      {
          insert += ", ?";
      }
      insert += ")";
      
      long start = System.nanoTime();
      PreparedStatement preparedStatement = connection.prepareStatement(insert);
      
      for(int i=1; i<=NUM_INSERT; i++)
      {
        preparedStatement.setInt(1, i);
        for(int j=1; j<=NUM_TEXT; j++)
        {
          preparedStatement.setString(j + 1, "Text_" + j);
        }
        for(int j=1; j<=NUM_INTEGER; j++)
        {
          preparedStatement.setInt(NUM_TEXT + j + 1, j);
        }
        preparedStatement.executeUpdate();
      }
      
      connection.commit();
      long end = System.nanoTime();
      System.out.println("Total time: " + (float)(end-start)/1000000000 );
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
      e.printStackTrace(System.out);
    }
    finally
    {
      try
      {
        if(connection != null)
        {
          connection.close();
        }
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    
  }
}

class TestSqliteWrapper
{
    public static void doit()
    {
        String              query;
        SQLiteConnection    db                  = null;
        SQLiteStatement     st                  = null;

        try
        {
            /* Open connection */
            db              = new SQLiteConnection(new File("samplewrapper.db"));
            db.open(true);

            SQLiteStatement stmtDrop = null;
            try
            {
                db.exec("BEGIN");
                query           = "drop table if exists two_hundred_one";
                stmtDrop = db.prepare(query);
                stmtDrop.step();
                db.exec("COMMIT");
            }
            catch (Exception ee)
            {
                //System.out.println (ee.toString());
                db.exec("ROLLBACK");
            }
            finally
            {
                if (stmtDrop != null)
                {
                    stmtDrop.dispose();
                }
            }
            
            db.exec("BEGIN");
            String create = "create table two_hundred_one (recid integer";

            for(int i=1; i<=TestSqliteDriver.NUM_TEXT; i++)
            {
                create += ", text_" + i + " text";
            }
            for(int i=1; i<=TestSqliteDriver.NUM_INTEGER; i++)
            {
                create += ", int_" + i + " integer";
            }
            create += ")";
            db.exec(create);
            db.exec("COMMIT");
            
            db.exec("BEGIN");
            query = "insert into two_hundred_one (recid";
            for(int i=1; i<= TestSqliteDriver.NUM_TEXT; i++)
            {
                query += ", text_" + i;
            }
            for(int i=1; i<=TestSqliteDriver.NUM_INTEGER; i++)
            {
                query += ", int_" + i;
            }
            
            query += ") values(?";

            for(int i=0; i < TestSqliteDriver.NUM_TEXT + TestSqliteDriver.NUM_INTEGER; i++)
            {
                query += ", ?";
            }
            query += ")";

            long start = System.nanoTime();
            st  = db.prepare(query);
            for(int i=1; i<=TestSqliteDriver.NUM_INSERT; i++)
            {
                for(int j=1; j<=TestSqliteDriver.NUM_TEXT; j++)
                {
                    st.bind(j+1, "Text_" + j);
                }

                for(int j=1; j<=TestSqliteDriver.NUM_INTEGER; j++)
                {
                    st.bind(TestSqliteDriver.NUM_TEXT + j + 1, j);
                }

                st.bind(1, i);
                st.step();
                st.reset();
            }
            db.exec("COMMIT");
            long end = System.nanoTime();
            System.out.println("Total time: " + (float)(end-start)/1000000000 );
        }
        catch (Exception ee)
        {
            System.out.println ("Exception inserting record: " + ee);
            ee.printStackTrace(System.err);       // test
            System.exit(0);                      // test
        }
        finally
        {
            try
            {
                if (db != null)
                {
                    db.dispose();
                }
            }
            catch (Exception e) { }
            try
            {
                if (st != null)
                {
                    st.dispose();
                }
            }
            catch (Exception e) {}
        }
    }
}

H2