[JAVA] JTable, database e sistema di caching

[andbin]

Introduzione:
Ho realizzato un piccolo framework di classi per gestire in maniera "generica" la visualizzazione dei dati in una tabella di un database (MySQL) attraverso una JTable.
In pratica ho creato una classe DatabaseTableModel derivandola da AbstractTableModel. Questa classe la creo con una serie di parametri tra cui una Connection (di JDBC), il nome della tabella e le definizioni delle varie colonne e dei campi della tabella da usare/visualizzare.

Ho voluto fare le cose in modo molto generico. Non faccio quindi presupposizioni sul numero effettivo di righe nella tabella e non leggo in memoria tutta l'intera tabella (sarebbe assurdo/allucinante!).
Ho fatto una cosa un po' più complessa: ho creato un sistema di cache di tipo "direct-mapped". In pratica ho sempre al massimo un certo numero di righe in cache (in memoria).
Quando la JTable chiama il mio TableModel tramite il metodo:
public Object getValueAt (int row, int column)
io vado a vedere se la riga è in cache. Se non è in cache eseguo una query sulla tabella per leggere N righe (tra cui sicuramente quella richiesta), le metto in cache e poi in qualunque caso restituisco l'oggetto alla riga/colonna richiesta.

Problema:
Questa gestione funziona ma non in modo ottimale. Per le prove uso un database contenente i dati ABI-CAB, nominativo, ecc... di circa 35000 banche italiane.
Se parto dall'inizio e faccio page-down in continuazione va abbastanza bene. Ogni tanto si blocca un breve istante, presumibilmente proprio per via delle query.
La cosa invece va decisamente male quando dal fondo della tabella risalgo con page-up. È molto "scattoso" e va a rilento. Idem quando prendo la scrollbar della JTable e inizio a muovermi velocemente lungo la tabella.

Cosa posso fare?
Quali potrebbero essere le soluzioni per migliorare le prestazioni del mio DatabaseTableModel??
Attualmente le query le effettuo in modo sincrono all'interno del metodo getValueAt.
Può essere utile mettere la lettura in un thread separato? E come posso sincronizzarlo con le richieste dalla JTable? Potrei avere dei benefici se aggiungessi alla mia cache un sistema a N-way??

P.S.: L'argomento non è banale, lo so. Spero di aver spiegato bene la questione, in caso contrario, ditemelo.

[kingv]

se metti l'interrogazione del database in un thread separato ma lo lanci quando ti viene chiesto il valore di una certa riga non hai nessun incremento delle prestazioni.
dato che la modalità di scorrimento della tabelle più frequente sarà utilizando i tasti di paginazione potresti leggere in un thread separato i record successivi a quello richiesto, in modo da averli già in cache per la successiva richiesta.

usi un prodotto per la cache o te la sei scritta tu?

[andbin]

Originariamente inviato da kingv

se metti l'interrogazione del database in un thread separato ma lo lanci quando ti viene chiesto il valore di una certa riga non hai nessun incremento delle prestazioni.
dato che la modalità di scorrimento della tabelle più frequente sarà utilizando i tasti di paginazione potresti leggere in un thread separato i record successivi a quello richiesto, in modo da averli già in cache per la successiva richiesta.

Ci ho pensato anche io ma bisogna vedere come farlo praticamente. L'ideale sarebbe quello di avere sempre in cache un certo numero di righe che sono "attorno" alla riga corrente della tabella. Se sono sulla riga 1000, potrei tenere in cache ad esempio tutte le righe dalla 900 alla 1100.

Questo in un certo senso lo faccio già. Se la riga N mi da un "miss" sulla cache, allora leggo (per il momento in modo sincrono) tutte le righe da N-M a N+M (dove M è un valore che parametro io, es. 50, 100, ecc...).
C'è solo un piccolo inconveniente. L'esempio di prima: viene chiesta la riga 1000, non è in cache, allora leggo da 900 a 1100. Il fatto è che la prossima lettura avverrà quando ci sarà un altro "miss", esempio la riga 1101. Così siamo daccapo. Se invece mi muovessi verso la riga es. 1080 (ancora in cache), il mio TableModel dovrebbe essere abbastanza "furbo" da capire che si sta andando verso dei dati non in cache e quindi fare in modo asincrono la lettura di altre 100 righe successive. Così avrei molti meno "miss".

Comunque il TableModel non sa quale è la riga selezionata nel JTable. Gli viene solo chiesto tramite il metodo getValueAt, "dammi l'elemento sulla tal riga/colonna".

Originariamente inviato da kingv

usi un prodotto per la cache o te la sei scritta tu?

Me la sono scritta io.
Anzi se vuoi la riporto qui, nulla di così eccezionale!

import java.lang.*;
import java.util.*;

public class DatabaseCacheRow
{
    private long rowIndex;
    private HashMap<String,Object> hash;

    public DatabaseCacheRow (long rowIndex)
    {
        this.rowIndex = rowIndex;
        hash = new HashMap<String,Object> ();
    }

    public long getRowIndex ()
    {
        return rowIndex;
    }

    public Object getElement (String key)
    {
        if (key != null)
            return hash.get (key);
        else
            return null;
    }

    public void putElement (String key, Object element)
    {
        if (key != null && element != null)
            hash.put (key, element);
    }
}

DatabaseCacheRow è una semplice classe che contiene il numero della riga (relativo alla tabella nel db) e un HashMap con i valori della riga, es: "abi"->"01025" e "cab"->"01000", ecc....

import java.lang.*;

public class DirectMappedDatabaseCache
{
    private int cacheSize;
    private DatabaseCacheRow[] cacheRows;

    public DirectMappedDatabaseCache (int cacheSize)
    {
        this.cacheSize = cacheSize;
        cacheRows = new DatabaseCacheRow[cacheSize];
    }

    public boolean hasRow (long rowIndex)
    {
        int cacheIdx = (int) rowIndex % cacheSize;

        if (cacheRows[cacheIdx] != null && cacheRows[cacheIdx].getRowIndex () == rowIndex)
            return true;
        else
            return false;
    }

    public DatabaseCacheRow getRow (long rowIndex)
    {
        int cacheIdx = (int) rowIndex % cacheSize;

        if (cacheRows[cacheIdx] != null && cacheRows[cacheIdx].getRowIndex () == rowIndex)
            return cacheRows[cacheIdx];
        else
            return null;
    }

    public void setRow (DatabaseCacheRow cacheRow)
    {
        int cacheIdx = (int) (cacheRow.getRowIndex () % cacheSize);

        cacheRows[cacheIdx] = cacheRow;
    }

    public void flushRow (long rowIndex)
    {
        int cacheIdx = (int) rowIndex % cacheSize;

        if (cacheRows[cacheIdx] != null && cacheRows[cacheIdx].getRowIndex () == rowIndex)
            cacheRows[cacheIdx] = null;
    }

    public void flushAll ()
    {
        for (int i = 0; i < cacheRows.length; i++)
            cacheRows[i] = null;
    }

    public int size ()
    {
        return cacheSize;
    }
}

DirectMappedDatabaseCache è invece la classe per la cache "direct-mapped". Credo che si noti chiaramente che una certa riga del db (indicata da rowIndex) può andare esclusivamente in una sola riga della cache!

[kingv]

Originariamente inviato da andbin

Comunque il TableModel non sa quale è la riga selezionata nel JTable. Gli viene solo chiesto tramite il metodo getValueAt, "dammi l'elemento sulla tal riga/colonna".

è vero ma probabilmente quel metodo verrà richiamato da jtable nel momento in cui deve "disegnare" quello che appare sullo schermo (e quindi necessita dei dati dal modello), per cui puoi sapere se ti stai avvicinando alle righe di cui non hai fetchato i valori e interrogare il db (qui sì su un thread separato) per recuperarli.
certo non è banale, il tablemodel deve incorporare un po' di "intelligenza".

[andbin]

Originariamente inviato da kingv

è vero ma probabilmente quel metodo verrà richiamato da jtable nel momento in cui deve "disegnare" quello che appare sullo schermo (e quindi necessita dei dati dal modello), per cui puoi sapere se ti stai avvicinando alle righe di cui non hai fetchato i valori e interrogare il db (qui sì su un thread separato) per recuperarli.
certo non è banale, il tablemodel deve incorporare un po' di "intelligenza".

Grazie mille, vedrò cosa fare per le letture in modo asincrono. Al massimo scrivo di nuovo se ho altri dubbi.

C'è invece un'altra cosa che mi lascia un po' perplesso. Il fatto che con una riga di partenza molto alta nella clausola LIMIT, la query impiega molto tempo ad essere eseguita.
Mi spiego meglio: io ho sempre la mia tabella delle banche (circa 35000 record). Nel punto in cui faccio la query ho inserito delle istruzioni per determinare quanto tempo ci impiega. Una cosa del tipo:

Statement stmt = conn.createStatement ();

long t1 = System.currentTimeMillis ();
ResultSet rset = stmt.executeQuery (strQuery);
long t2 = System.currentTimeMillis ();

La query che faccio è davvero semplice, non ha (al momento) nemmeno clausole WHERE o ORDER BY.

Se faccio una query con una riga di partenza bassa, esempio:
SELECT campi,.... FROM banche LIMIT 100,40
ci mette in media sui 20 millisecondi.

Se faccio una query con una riga di partenza alta, esempio:
SELECT campi,.... FROM banche LIMIT 34000,40
ci mette da 500 a 850 millisecondi.

Ecco il motivo per cui al fondo della tabella, il mio JTable si comporta in modo molto "scattoso".
Ora ... non credo di aver scoperto l'acqua calda. Il database, per poter skippare 34000 righe e arrivare a quella che gli chiedo, probabilmente fa delle letture e/o dei filtraggi sui record in modo sequenziale e quindi impiega del tempo!
C'è qualche modo di evitare questo problema? Qualche ottimizzazione o modifica alla query per migliorare i tempi??

[kingv]

è strano, la differenza mi sembra troppa.

la query è semplice o tocca diverse tabelle?
che tipo di tabelle sono?
hai indici?
cambia qualcosa se lanci una ANALYZE prima della query?

domani se ho tempo faccio qualche prova, è interessante.

[Angus]

perdonate la mia ignoranza ma:

. A quale idioma SQL appartiene il token LIMIT?
. Non è stato specificato qualcosa riguardo la gestione della connessione al DB.

Riguardo al secondo punto, per la funzionalità particolare che si vuole implementare (vista dei dati in sola lettura non transazionale), sarebbe opportuno provare ad utilizzare una connessione alla tabella sempre attiva ed in sola lettura (non isolata dal punto di vista transazionale) con un cursore non FORWARD_ONLY e spostarlo come comanda l'utente.

Vorrei piazzare un bel disclaimer per le cassate eventualmente dette ma a quest'ora sono troppo cotto

[kingv]

Originariamente inviato da Angus

perdonate la mia ignoranza ma:

. A quale idioma SQL appartiene il token LIMIT?

a quello di mysql, ma ogni db ha la sua sintassi per limitare i risultati ottenuti.

[andbin]

Grazie mille a kingv e Angus per le risposte.
A me interessa davvero molto realizzare con Java un qualcosa di utile e valido per gestire i database e se qualcuno mi aiuta con qualche suggerimento/consiglio ... mi fa molto piacere!

Chiarisco subito come è fatta la tabella delle banche che sto usando. Premetto che sto usando MySQL 5 (5.0.18-nt) su Windows 2000.

CREATE TABLE banche (
  abi CHAR(5)        NOT NULL,
  cab CHAR(5)        NOT NULL,
  banca CHAR(80)     NOT NULL,
  filiale CHAR(60)   NOT NULL,
  indirizzo CHAR(80) NOT NULL,
  citta CHAR(40)     NOT NULL,
  cap CHAR(16)       NOT NULL,
  PRIMARY KEY(abi,cab),
  KEY(banca)
);

La tabella l'ho popolata partendo da un file di testo (opportunamente formattato) e caricandolo nella tabella con la direttiva "LOAD DATA ...". Come ho già avuto modo di dire, ho circa 35000 record nella tabella.

Ho fatto anche delle prove "a mano" (che non centrano nulla con Java, quindi) con il tool a linea di comando del MySQL.

Esempio:
mysql> SELECT * FROM banche LIMIT 34000,40;
[...]
40 rows in set (0.54 sec)

Ho provato anche qualcosa di un po' più complesso:
mysql> SELECT * FROM banche ORDER BY banca DESC LIMIT 34000,40;
[...]
40 rows in set (3.29 sec)

Ora ... io non sono ancora molto esperto di database ma 3,29 secondi per fare una query del cavolo usando 1 sola tabella, 1 sola connessione al db e senza avere null'altro in "carico" sul PC ... beh, mi sembra un po' troppo.
È così o invece è "normale"??? Cosa ne pensate??

Grazie

[kingv]

ho fatto un paio di prove.

tra le tue due query anche sulla mia macchina la differenza è notevole, ho creato una tabella simile con circa 39mila righe e la query con l'order by è almeno 20 volte più lenta dell'altra e penso che la differenza cresca all'aumentare del numero delle righe.

così su due piedi non riesco a capire il perchè

[andbin]

Originariamente inviato da kingv

ho fatto un paio di prove.

tra le tue due query anche sulla mia macchina la differenza è notevole, ho creato una tabella simile con circa 39mila righe e la query con l'order by è almeno 20 volte più lenta dell'altra e penso che la differenza cresca all'aumentare del numero delle righe.

così su due piedi non riesco a capire il perchè

Grazie per la risposta (e per la prova).

Sul mio PC oltre al MySQL 5 ho anche il PostgreSQL 8.1. Volevo provare a fare la stessa cosa anche sul PostgreSQL. Visto che non lo conosco ancora bene, quale è il modo più semplice e veloce per trasferire la mia tabella dal MySQL al PostgreSQL??? Nota: ho già creato sul PostgreSQL lo stesso database, lo stesso utente ed anche la stessa tabella. Per quest'ultima quando ho fatto la CREATE TABLE mi ha dato errore sulla clausola KEY. Ho quindi aggiunto a "mano" dal tool di amministrazione del PG l'indice per il campo banca.

[sottovento]

Originariamente inviato da kingv

ho fatto un paio di prove.

tra le tue due query anche sulla mia macchina la differenza è notevole, ho creato una tabella simile con circa 39mila righe e la query con l'order by è almeno 20 volte più lenta dell'altra e penso che la differenza cresca all'aumentare del numero delle righe.

così su due piedi non riesco a capire il perchè

Ciao,
stavo seguendo i vostri discorsi. La prova con Postgres (stando a quanto leggo fra i documenti) e' molto interessante poiche' sembra sia il database migliore per queste cose. (hai query semplici e devono essere veloci).

Le prestazioni che hai ottenuto con MySql sono quelle che mi aspettavo, purtroppo.
Domanda: MySql dispone di una pletora di strumenti per l'analisi delle query. Hai provato ad analizzare la tua query con questi strumenti?
Inoltre questi strumenti ti dicono anche quali indici MySql ha deciso di utilizzare per ottimizzare il tempo di esecuzione della query.
Hai un'idea di quali siano?

Nella definizione della tabella, hai una chiave primaria composta da due campi di tipo stringa. Questo non e' il massimo per quanto riguarda le prestazioni.
In ogni caso, MySql creera' almeno 3 indici: uno per ogni singolo campo della chiave primaria ed uno per la coppia di campi.

Mi chiedevo se non ti conveniva definirti un TUO campo (intero) come chiave primaria. Sicuramente le query sarebbero piu' veloci e probabilmente anche il tuo programma ne avrebbe dei benefici.
Se infatti numeri tutti i record, la ricerca sulla chiave primaria sara' sicuramente piu' veloce. Inoltre anche per te, selezionare i record di una pagina e' "quasi banale" (SELECT .........WHERE indice > .. AND indice < ...).

Immagino che accederai, per il 90% del tempo, a questa tabella per semplice consultazione. A regime, avrai un numero limitato di inserimenti/aggiornamenti.
Pertanto hai un'ampia possibilita' di ottimizzare sfruttando al meglio gli indici...

High Flying
Sottovento

[kingv]

ora sono a casa e non installo niente che abbia a che fare col lavoro sennò la dolce metà mi mena , per cui rimando a settimana prossima le prove con postgre (che purtroppo conosco poco)


l'idea che mi sono fatto è che l'indice sul campo banca puo' essere usato per trovare un record e (forse) per farne il sort, ma non per "contarlo" usando limit; questo spiegherebbe almeno in parte perchè quando la selezione è fatta all'inizio della lista e' veloce e quando è alla fine è lenta.

il suggerimento di sottovento è sensato, anche se in generale odio le tabelle con un campi "contatore" (a che sevono le chiavi primarie altrimenti )

[cagnaluia]

riesumo..

niente.. ho un problema con jTable e database..

fino a qualche minuto fa usavo un metodo molto rudimentale.. attraverso il quale riempivo ogni campo di ogni riga... jTable1.setValueAt(rs.getString(i),riga,i-1);


mi si dice di usare tablemodel..

ma non ho per niente capito come fare...

[dupa]

tanto per cominciare devi mettere Indici sulle colonne dove fai WHERE e ORDER BY.
poi ipotizzo che quando uno fa "page-down", la Jtable faccia quasi contemporaneamente tante "getRowAtPosition" per tutte le righe che son diventate visibili.. per usare in modo utile il tuo sistema di caching ti conviene metter nel tuo cacher dei "synchronized" nei punti giusti in modo da far sì che se la Jtable sta chiedendo tante righe vicine, venga fatta una sola query SQL e tutte le altre richieste messe in coda possano beneficiare del caching fatto dalla prima richiesta che ha passato il synchronized.

ciao