sqlite jdbc

[cdimauro]

Originariamente inviato da wingman87

Giusto! Grande! Appena ho tempo aggiungo la misurazione del tempo ma sul mio pc non so se sarà molto significativo senza fare un confronto con la versione java perché ho un hd ssd...
Comunque a naso ci mette circa 20 secondi.

Da quel che ha scritto anche sottovento sembra che il suo problema sia I/O bound. Un SSD aiuta di certo in questi casi, ma la CPU rimarrà spesso a girarsi i pollici.

@sottovento: se i file da cui estrarre le informazioni sono in formato csv, Python ha un modulo già integrato ad hoc. Al solito, basta dare un'occhiata veloce agli esempi per capire come usarlo (il reader è semplicissimo).

[Vincenzo1968]

[VICIUS]

Originariamente inviato da sottovento

A questo punto e' piu' facile provare un altro database, pero'.

L'ultima volta che mi è capitato di dover usare un db embedded ho provato H2 (http://www.h2database.com/) e mi ci sono trovato veramente bene.

Originariamente inviato da cdimauro

Da quel che ha scritto anche sottovento sembra che il suo problema sia I/O bound. Un SSD aiuta di certo in questi casi, ma la CPU rimarrà spesso a girarsi i pollici.

Se il problema è veramente l'i/o e il db non è enorme si potrebbe provare ad usare un ramdisk. Su una macchina linux basta creare il file in /dev/shm.

[sottovento]

Originariamente inviato da Vincenzo1968

Nientedimeno se ne trovano di già bell'e pronti:

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



EDIT: Eh! va che roba:

Provato, non senza fatica vista la mancanza di documentazione.
Prima impressione: wow! Va come una sgiavella! Quando prima mi servivano una ventina di minuti per fare il lavoro che mi serviva, ora servono una trentina di secondi, tempo piu' che accettabile per quel che devo fare.


Piccolo problema: ho dei record con una marea di campi, e la stringa di insert fallisce quando e' troppo lunga. Devo studiare una soluzione (probabilmente dovro' spezzare la query in una serie di insert + update).

[sottovento]

Originariamente inviato da VICIUS

L'ultima volta che mi è capitato di dover usare un db embedded ho provato H2 (http://www.h2database.com/) e mi ci sono trovato veramente bene.

E' vero, H2 e' davvero un database dalle ottime caratteristiche.

Originariamente inviato da VICIUS

Se il problema è veramente l'i/o e il db non è enorme si potrebbe provare ad usare un ramdisk. Su una macchina linux basta creare il file in /dev/shm.

Beh, nel caso in questione il problema era palesemente il driver. Cmq il suggerimento e' buono, anche se non applicabile nel mio caso. Grazie comunque!

[Vincenzo1968]

Originariamente inviato da sottovento

Provato, non senza fatica vista la mancanza di documentazione.
Prima impressione: wow! Va come una sgiavella! Quando prima mi servivano una ventina di minuti per fare il lavoro che mi serviva, ora servono una trentina di secondi, tempo piu' che accettabile per quel che devo fare.


Piccolo problema: ho dei record con una marea di campi, e la stringa di insert fallisce quando e' troppo lunga. Devo studiare una soluzione (probabilmente dovro' spezzare la query in una serie di insert + update).

[wingman87]

Alla fine il tempo l'ho misurato con time.clock() e time.perf_counter(), ci mette dai 16 ai 19 secondi.
Il db finale è grande circa 110 MB.

In python 2.7:

import sqlite3
import time

start = time.clock()

conn = sqlite3.connect('example.db')
c = conn.cursor()

NumStrings = 100
NumIntegers = 100

# Create table
c.execute('create table two_hundred_one (id integer, %s)' % ', '.join(['str_%s text' % i for i in xrange(1, NumStrings + 1)] + ['int_%s integer' % i for i in xrange(1, NumIntegers + 1)]))

t = tuple(['Text_%s' % i for i in xrange(1, NumStrings + 1)] + range(1, NumIntegers + 1))

Query = 'insert into two_hundred_one values (%s' + ', ?' * (NumStrings + NumIntegers) + ')'

for id in xrange(1, 100001):
  c.execute(Query % id, t)
  
# Save (commit) the changes
conn.commit()

# We can also close the cursor if we are done with it
c.close()

end = time.clock()
print('Total time: ' + str(end - start))

In python 3.3 (sembrerebbe circa un secondo più lento ma visti i tempi variabili non ci metterei la mano sul fuoco):

import sqlite3
import time

NUM_TEXT = 100
NUM_INTEGER = 100

start = time.perf_counter()
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('create table two_hundred_one (id integer, %s)' % ', '.join(['str_%s text' % i for i in range(1, NUM_TEXT + 1)] + ['int_%s integer' % i for i in range(1, NUM_INTEGER + 1)]))

t = tuple(['Text_%s' % i for i in range(1, NUM_TEXT + 1)] + list(range(1, NUM_INTEGER + 1)))

insert = 'insert into two_hundred_one values (%s' + ', ?' * (NUM_TEXT + NUM_INTEGER) + ')'
for id in range(1, 100001):
    c.execute(insert % id, t)
	
# Save (commit) the changes
conn.commit()

# We can also close the cursor if we are done with it
c.close()

end = time.perf_counter()

print('Total time: ' + str(end - start))

[cdimauro]

Originariamente inviato da VICIUS

Se il problema è veramente l'i/o e il db non è enorme si potrebbe provare ad usare un ramdisk. Su una macchina linux basta creare il file in /dev/shm.

E' da un pezzo che ci penso, ma ti confesso che è una gran rottura di scatole.

Ho un progetto che usa MySQL, e una batteria con circa 400 test, che impiega circa 4 minuti per essere eseguita ogni volta. Purtroppo non esiste modo di spostare programmaticamente il data store da un'altra parte. Con SQLite o Firebird posso decidere di aprire file ovunque, mentre con MySQL dovrei: fermare il server, copiare tutti i file dalla datadir alla ram disk, e farlo ripartire. Fattibile se hai una datadir piccola, ma se contiene diversi database e supera il GB, diventa troppo pesante.

Originariamente inviato da sottovento

Piccolo problema: ho dei record con una marea di campi, e la stringa di insert fallisce quando e' troppo lunga. Devo studiare una soluzione (probabilmente dovro' spezzare la query in una serie di insert + update).

Questo è strano. Se usi le query mettendo dei placeholder per i parametri, per poi passarli direttamente lasciando che sia il driver a inviarli al db, non dovresti avere alcun problema di questo tipo. A meno che non sia proprio un limite di SQLite.

Originariamente inviato da wingman87

Alla fine il tempo l'ho misurato con time.clock() e time.perf_counter(), ci mette dai 16 ai 19 secondi.
Il db finale è grande circa 110 MB.

Mi sembra un ottimo tempo. La virtual machine influisce poco per questo tipo di problemi.

In python 3.3 (sembrerebbe circa un secondo più lento ma visti i tempi variabili non ci metterei la mano sul fuoco):

E' possibile, perché in Python 3.x la gestione degli interi è un po' più lenta rispetto alla 2.x. Roba di poco conto, comunque; infatti hai appena un secondo di scarto.

[sottovento]

Originariamente inviato da cdimauro

Questo è strano. Se usi le query mettendo dei placeholder per i parametri, per poi passarli direttamente lasciando che sia il driver a inviarli al db, non dovresti avere alcun problema di questo tipo. A meno che non sia proprio un limite di SQLite.

Sembra davvero un limite di sqlite. Uso i placeholder ma mi risponde che la stringa e' troppo lunga. Pero' i miei record hanno piu' di 2000 campi, e questo puo' essere un problema

[cdimauro]

Allora è molto probabile che sia quello il problema. Anche se mi sembra veramente strano: è un limite incomprensibile.

[The_ouroboros]

Ellapepa quanti attributi...

Inviato dal mio Sony Xperia P

[cdimauro]

Trovato il problema. Da qui:

Maximum Number Of Host Parameters In A Single SQL Statement

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

[sottovento]

Bene! Beh, mi sembra che siano stati risolti tutti i problemi, no?
Le prestazioni scadenti erano dovuti al driver (o meglio, alla sua implementazione), e sappiamo anche perche' le mie query fallivano.

Grazie a tutti!!

[Vincenzo1968]

Aspetta aspetta!

Perché non ci spieghi un attimino come funziona sqlite4java visto che la documentazione su GoogleCode è carente?
Una sorta di mini corso, un "getting started" migliore di quello che si trova nel sito ufficiale.

[sottovento]

Originariamente inviato da Vincenzo1968

Aspetta aspetta!

Perché non ci spieghi un attimino come funziona sqlite4java visto che la documentazione su GoogleCode è carente?
Una sorta di mini corso, un "getting started" migliore di quello che si trova nel sito ufficiale.

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

[wingman87]

Ho riscritto lo stesso test anche in java e penso di aver trovato l'inghippo.
Posto le due versioni, quella lenta e quella veloce:

Lenta:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class TestSqlite
{
  public static int NUM_TEXT = 100;
  public static int NUM_INTEGER = 100;
  public static int NUM_INSERT = 10000;

  public static void main(String[] args) throws ClassNotFoundException
  {
  
    long start = System.nanoTime();
    
    // 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:sample.db");
      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 += ")";
      
      PreparedStatement preparedStatement = connection.prepareStatement(insert);
      for(int i=1; i<=NUM_TEXT; i++){ preparedStatement.setString(i + 1, "Text_" + i); }
      for(int i=1; i<=NUM_INTEGER; i++){ preparedStatement.setInt(NUM_TEXT + i + 1, i); }
      
      for(int i=1; i<=NUM_INSERT; i++){
        preparedStatement.setInt(1, i);
        preparedStatement.executeUpdate();
      }
      
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    
    long end = System.nanoTime();
    
    System.out.println("Total time: " + (float)(end-start)/1000000000 );
  }
}

In questa versione ho effettuato solo 10000 insert ma il tempo impiegato (l'ho fatto girare una sola volta) è stato 67 secondi

Ed ecco invece la versione veloce:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

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

  public static void main(String[] args) throws ClassNotFoundException
  {
  
    long start = System.nanoTime();
    
    // 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:sample.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 += ")";
      
      PreparedStatement preparedStatement = connection.prepareStatement(insert);
      for(int i=1; i<=NUM_TEXT; i++){ preparedStatement.setString(i + 1, "Text_" + i); }
      for(int i=1; i<=NUM_INTEGER; i++){ preparedStatement.setInt(NUM_TEXT + i + 1, i); }
      
      for(int i=1; i<=NUM_INSERT; i++){
        preparedStatement.setInt(1, i);
        preparedStatement.executeUpdate();
      }
      
      connection.commit();
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
    
    long end = System.nanoTime();
    
    System.out.println("Total time: " + (float)(end-start)/1000000000 );
  }
}

In questa ho usato gli stessi valori della versione python e il tempo impiegato è intorno ai 14 secondi, meglio di python che già mi sembrava sorprendente!

Cosa cambia nella seconda versione? Che ho usato una transazione. In sostanza la versione lenta scriveva su disco a ogni insert o quasi, infatti da esplora risorse vedevo il file del db crescere pian pianino.

[Vincenzo1968]

Perfetto, Sottovento, grazie mille

P.S.: l'esempio che ho postato l'ho copincollato dalla pagina "getting started" del sito ufficiale. Mi sembrava un po' pochino così ti ho chiesto questa miniguida. A complemento della documentazione ufficiale.

[sottovento]

Originariamente inviato da wingman87

Ho riscritto lo stesso test anche in java e penso di aver trovato l'inghippo.
Posto le due versioni, quella lenta e quella veloce:

Lenta:
<cut>

In questa versione ho effettuato solo 10000 insert ma il tempo impiegato (l'ho fatto girare una sola volta) è stato 67 secondi

Ed ecco invece la versione veloce:
<cut>

In questa ho usato gli stessi valori della versione python e il tempo impiegato è intorno ai 14 secondi, meglio di python che già mi sembrava sorprendente!

Cosa cambia nella seconda versione? Che ho usato una transazione. In sostanza la versione lenta scriveva su disco a ogni insert o quasi, infatti da esplora risorse vedevo il file del db crescere pian pianino.

Quindi la differenza di prestazioni e' dovuta all'autocommit? Sicuramente e' ragionevole.
A questo punto, appena ho tempo, cerchero' di fare il confronto fra versione con autocommit = false e quella scritta con le primitive suggerite da Vincenzo, per capire quanto overhead si porta via il driver... penso sia interessante per capire quali margini ci sono, i.e. se vale la pena esser standard o esser veloci

[VICIUS]

Originariamente inviato da cdimauro

E' da un pezzo che ci penso, ma ti confesso che è una gran rottura di scatole.

Ho un progetto che usa MySQL, e una batteria con circa 400 test, che impiega circa 4 minuti per essere eseguita ogni volta. Purtroppo non esiste modo di spostare programmaticamente il data store da un'altra parte. Con SQLite o Firebird posso decidere di aprire file ovunque, mentre con MySQL dovrei: fermare il server, copiare tutti i file dalla datadir alla ram disk, e farlo ripartire. Fattibile se hai una datadir piccola, ma se contiene diversi database e supera il GB, diventa troppo pesante.

4 minuti per soli 400 sono veramente tanti. Lavorare così deve essere frustrante. I tempi di esecuzione dei test come sono distribuiti? Se sono solo un paio di test che prendono la maggior parte del tempo potresti pensare di separarli dal resto della suite e lanciarli tutti solo prima di un commit. Con junit è piuttosto facile fare una cosa simile usando @Category.

Originariamente inviato da sottovento

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

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.

[cdimauro]

Originariamente inviato da VICIUS

4 minuti per soli 400 sono veramente tanti. Lavorare così deve essere frustrante.

Già. E' odioso.

I tempi di esecuzione dei test come sono distribuiti? Se sono solo un paio di test che prendono la maggior parte del tempo potresti pensare di separarli dal resto della suite e lanciarli tutti solo prima di un commit.

Purtroppo sono una miriade di test abbastanza piccoli, senza nessuno che sia pesante / preponderante. Il collo di bottiglia è rappresentato proprio da MySQL, e non ci si può fare niente.

Al più, visto che ho 4 core, potrei pensare di parallelizzarli, ma sarebbe un lavoraccio pure quello (dovrei creare 4 alias per il database, e lanciare 4 processi, uno per ogni core, che si smazzano ognuno 1/4 dei test), però l'hd è soltanto uno, ed è pure meccanico (niente SSD finora), per cui non so quanto si potrebbe guadagnare alla fine.

Credo che mi convenga sbattere la testa cercando qualche soluzione per usare una ram disk. Stavo pensando di creare una datadir soltanto per questo progetto, che quindi occuperà poco spazio. Tutti gli altri database staranno nel datadir che occupa molto più spazio. Creando qualche script per impostare il datadir desiderato, dovrei riuscire a cavarmela in qualche modo.

Con junit è piuttosto facile fare una cosa simile usando @Category.

Uso Python...