[Risolto] DB.SqLite - volumi supportabili

[Picavbg]

Buongiorno a tutti.
Come alcuni di voi sanno, l'unico mio programmone in Gambas3 si chiama ContabFam e gestisce all'interno delle sue classi un DB Sqlite3.
Quest'ultimo, dopo anni di accumulo dati, distribuiti nelle sue 13 tabelle , ha raggiunto volumi ragguardevoli. Ordinariamente i tempi di risposta sono eccellenti, tranne che quando avvio la ricerca per la stampa dei movimenti contabili registrati per una giornata qualunque delle di tutte quelle presenti, nel DB.
Per la ricerca dei movimenti viene eseguito un 'accesso al DB per  lo scorrimento dei record contenuti principalmente nella tabella dei movimenti giornalieri di cassa che contiene al momento oltre 54000 record (complessivamente il DB è formato da circa 106000 record). Per la precisione,  dalla schedulazione dell'evento, alla emissione della finestra contenenti i dati richiesti, passano circa 24". Mi sembrano veramente tanti.
Rispetto a circa 2 anni fa il tempo d'attesa è più che raddoppiato , perciò penso che continuando ad ingrossare il volume del DB, più avanti, potrò benissimo avviare la richiesta, e nel frattempo, andare a prendere un caffè al bar. Al mio rientro, troverò la finestra coi movimenti della giornata. 
Allora ... per evitare  di prendere dover troppi caffè, sto cercando una soluzione, in modo da fare rientrare il tempo di risposta ad un intervallo ragionevole per un minielaboratore.
La mia lunga esperienza nel campo dell'elaborazione dei dati mi dice che le strade possibili sono 2, alternativa l'una rispetto all'altra:
- indicizzare tutte le tabelle del DB;
- frazionare il DB attuale un più DB, di ridotte dimensioni.
Le due alrrenative sono entrambi onerose, ma io non ne conosco altre.
Ricordo che all'epoca della realizzazione del mio progetto non sono riuscito a creare un indice nel DB in questione.
Chi mi vuole dare una mano ad avviare uno studio sulla riorganizzazione del DB?

[Gianluigi]

Per creare un indice in SQLite puoi leggere qui:
https://sqlite.org/lang_createindex.html
https://www.w3schools.com/sql/sql_create_index.asp
Gli indici vanno impostati su tabelle già create (e riempite).
Normalmente la/le colonne sono NOT NULL UNIQUE e quindi si usa la formula:

Codice: [Seleziona]

CREATE UNIQUE INDEX "nome-indice" ON nome-tabella (nome-colonna ASC o DESC);

Se i dati non sono univoci basta togliere UNIQUE.

Però visto che scrivi

...(complessivamente il DB è formato da circa 106000 record). Per la precisione,  dalla schedulazione dell'evento, alla emissione della finestra contenenti i dati richiesti, passano circa 24". Mi sembrano veramente tanti. Rispetto a circa 2 anni fa il tempo d'attesa è più che raddoppiato...

credo che i dati siano veramente troppo pochi per causare un tale rallentamento e già due anni fa i tempi erano biblici.
A meno che tu non sia su un computer veramente lento in generale, non escluderei che le cause della lentezza siano dovute ad altri motivi, infatti poco più di 100.000 record sono una bazzecola anche per SQLite che può sopportare e trattare milioni di records.

Su questo attendo il parere degli esperti di DB.
Comunque se creando l'indice non cambia nulla..

[Picavbg]

Gli indici vanno impostati su tabelle già create (e riempite).
Normalmente la/le colonne sono NOT NULL UNIQUE

Ormai è passato troppo tempo dalla creazione del DB e non ricordo assolutamente come sono definite le colonne delle singole tabelle. Devo rivedere tutto, compresa la gestione del DB nel programma perchè. penso che, indicizzando le tabelle, cambi anche il codice scritto per la lettura e l'aggiornamento dei record in esso contenuti, nonchè per la registrazione di nuovi record.

non escluderei che le cause della lentezza siano dovute ad altri motivi,

Ho provato qualche mese fa a sostituire il disco dei dati con uno di tipo SSD, ma il risultato non è cambiato, così ho continuato ad usare un disco SATA.
Credo inoltre che se il problema fosse dipendente dalla vetustà del mio pc, il problemma della lentezza si dovrebbe verificare sempre, invece si verifica solo per la ricerca dei record appartenenti alla stessa data.
 

[Gianluigi]

Se ho capito bene devi indicizzare la colonna (o sono più di una?) delle date che tu hai in formato txt.
Non credo che tu qui possa avere dei campi vuoti, faresti bene a controllare.
Probabilmente i rallentamenti sono dovuti al campo txt e a possibili NULL.
Dovresti postare la query di interrogazione e i codici di formattazione ecc. che riguardano questa colonna.

[tornu]

Ciao Picavbg,
io non ho mai usato SqLite, per i miei progetti sia piccoli che "grandi" ho sempre utilizzato MySql
che sicuramente a livello di gestione è similare ma dispone di strumenti e funzionalità che sicuramente
mancano a SqLite. Ma nel tuo caso dove parliamo di circa 106000 record si può dire che sicuramente non
parliamo di un "grande" Database, quindi la lentezza che hai riscontrato nel tempo nell'estrapolare i dati
tramite le tue query sicuramente è data da una ottimizzazione non corretta del DB. L'indicizzazione delle
tabelle sicuramente è la prima cosa da verificare e implementare se non presenti, è una cosa basilare,
noterai delle differenze abissali. Ti posto questo piccolo estratto da letture e informazioni acquisite nel tempo
per far capire a te (ma sicuramente già lo sai) e a chiunque approcci i DataBase.
Ottimizzare database MySQL e velocizzare le query: un esempio concreto
Si supponga, come "esempio estremo", di avere la seguente tabella MySQL:

CREATE TABLE dipendenti (
matricola char(10) NOT NULL,
nome varchar(40),
cognome varchar(40),
indirizzo text(150),
telefono varchar(25),
salario int(11),
salario_straordinario int(10) NOT NULL
);

Per estrarre dal database il dato relativo al salario del dipendente contraddistinto dall'identificativo 693369, si utilizzerà la query che segue:

SELECT salario FROM dipendenti WHERE matricola = '693369';

MySQL, però, di base non dispone di alcun "appoggio" per trovare rapidamente l'informazione cercata: dovrà quindi esaminare il contenuto dell'intera tabella (potenzialmente migliaia di record) per restituire il dato sul salario.
Utilizzando un indice, MySQL potrà viceversa trovare le informazioni richieste molto più rapidamente.
Il "segreto" consiste nel creare l'indice sul campo o sui campi per i quali si attiva la clausola WHERE di SQL.

Nell'esempio, se si crea un indice per il campo matricola, i record cercati saranno trovati da MySQL molto velocemente.

Gli indici, in MySQL, funzionano in maniera molto simile agli indici di un qualunque libro. Si immagini un testo tecnico sprovvisto di indice: per trovare il capitolo Ottimizzare MySQL bisognerà necessariamente sfogliare tutte le pagine; con un indice, invece, ci si potrà recare rapidamente alla pagina giusta. 

[Gianluigi]

Ormai è passato troppo tempo dalla creazione del DB e non ricordo assolutamente come sono definite le colonne delle singole tabelle. Devo rivedere tutto, compresa la gestione del DB nel programma perchè. penso che, indicizzando le tabelle, cambi anche il codice scritto per la lettura e l'aggiornamento dei record in esso contenuti, nonchè per la registrazione di nuovi record.

Se tu scarichi l'applicazione DB Browser for SQLite, puoi fare doppio clic sul tuo database per aprirlo nell'applicazione.
Nella scheda Create Table evidenzi la query CREATE TABLE... della tabella che ti interessa e poi dalla scheda (ora agibile) Modify Table puoi vedere come è stata costruita la tabella ed eventualmente variarla.
Non servono variazioni in quanto l'indicizzazione di una o più colonne di tabella non comporta variazioni nelle query di interrogazione, le rende solo più veloci come spiegato da Tornu.

[Picavbg]

Grazie per le indicazioni. Farò un controllo sul DB per mettere in pratica poi quanto mi avete cortesemente suggerito.
A presto.

[Gianluigi]

Ti allego un piccolo progetto che filtra i record sulla colonna Data (usdat) non indicizzata, a me lo fa in pochi attimi.
Così puoi verificare su una base concreta.
Nota il database si carica di dati automaticamente all'apertura e salva in "user.home/application.name"

P.S. Ho rimosso l'allegato potete scaricarlo da qui

[Picavbg]

Ti allego un piccolo progetto che filtra i record sulla colonna Data (usdat) non indicizzata, a me lo fa in pochi attimi.
Così puoi verificare su una base concreta.

Grazie Gianluigi. Sei sempre disponibile e ti sono grato.
La mia lettura scansionata della tabella  movimgg (quella incriminata, contenente oltre 54000 record) è fatta attraverso il seguente codice

Codice: [Seleziona]

 i_dataStamp = Int(Val($_DataStamp))
  ApriDB = New OpenDB
  $_TbParFrm.Add("0")
  i_RgGriStamp = (-1)
  RecMovgg = ApriDB.DBConnection.EXEC("SELECT * FROM movimgg WHERE DtCoMovgg = '" & i_dataStamp & "' ORDER BY NuProMovvgg")
  For Each RecMovgg
    $_LirEuro = RecMovgg!MonMovvggPuò darsi che esista DtCoMovgg
    i_RgGriStamp += 1
    GridStamp[i_RgGriStamp, 1].text = RecMovgg!DescrMovvgg
    fImpMov = RecMovgg!ImpMovvgg
    If fImpMov > 0 Then
        i_CaselGriStamp = 3
    Else
      fImpMov *= (-1)
      i_CaselGriStamp = 4
    Endif

Riporto anche il codice relativo alla Open del DB

Codice: [Seleziona]

'------------------------------- OpenDB.Class ------------------------------------------------------------------------------------------
Public bSwOpErr As Boolean

Public DBConnection As New Connection   'inizializza la nuova connessione

'**************************--- la prossima riga  vale per tutti ---***********************
Public $DbPath As String = user.home & "/mont/dativari/contabfam"             'Percorso di ricerca del Database ContabFam.db"
Public $IconPath As String = user.home & "/mont/dativari/contabfam/IconVar"   'Persocorso di ricerca delle picture utilizzate nel programma
'***********************************************************************************************
Public $DbNome As String = "ContabFamdb"        'Nome del Database

Public Sub _new()
'----------------------------------------
  With
      DBConnection
            .Close
            .Type = "sqlite3"
            .Host = $DbPath
            .Name = $DbNome
            .OPEN    'Apro il DB
  End With'------------------------------- OpenDB.Class ------------------------------------------------------------------------------------------
Public bSwOpErr As Boolean

Public DBConnection As New Connection   'inizializza la nuova connessione

'**************************--- la prossima riga  vale per tutti ---**************************
'Public $_DbPath As String = user.home & "/contabfam"                         'Percorso di ricerca del Database ContabFam.db"
Public $DbPath As String = user.home & "/mont/dativari/contabfam"             'Percorso di ricerca del Database ContabFam.db"
Public $IconPath As String = user.home & "/mont/dativari/contabfam/IconVar"   'Persocorso di ricerca delle picture utilizzate nel programma
'***********************************************************************************************
Public $DbNome As String = "ContabFamdb"        'Nome del Database

Public Sub _new()
'----------------------------------------
  With
      DBConnection
            .Close
            .Type = "sqlite3"
            .Host = $DbPath
            .Name = $DbNome
            .OPEN    'Apro il DB
  End With
  bSwOpErr = False    'OPEN eseguita correttamente
  Catch
    bSwOpErr = True     'Errore nella Open
End
  bSwOpErr = False    'OPEN eseguita correttamente
  Catch
    bSwOpErr = True     'Errore nella Open
End

Come puoi vedere l'istruzione di lettura della tabella, oggetto della mia sofferenza è quella di selezione dei record interessanti alla mia ricerca e del successivo caricamento in una GidView, attraverso l'istruzione for each:

Codice: [Seleziona]

RecMovgg = ApriDB.DBConnection.EXEC("SELECT * FROM movimgg WHERE DtCoMovgg = '" & i_dataStamp & "' ORDER BY NuProMovvgg")
  For Each RecMovgg

L'istruzione SQL "EXEC" procede alla scansione dei record di tabella riportanti la stessa data contabile (DtCoMovgg),  disponendoli altresìi in ordine di numero prograssivo di operazione.
Io ho visto che tu non hai adoperato istruzioni SQL. Che sia proprio l'istruzione SQL a provocare rallentamenti così esasperanti?
Non sono in grado di valutarlo. Ricordo che allora ho faticato non poco per mettere a punto l'orologio gestionale del mio DB attraverso i DB.EXEC.
Esiste un metodo più semplice? Non lo so. A me è venuto allora facile e comprensibile costruirlo così.

[Gianluigi]

Io ho visto che tu non hai adoperato istruzioni SQL. Che sia proprio l'istruzione SQL a provocare rallentamenti così esasperanti?

Io uso solo istruzioni SQL, è che amo tenere le cose in ordine e tutto l'SQL che mi serve lo riunisco in un modulo (MBase.module).
Poi utilizzo funzioni pubbliche per interrogare il database.
In questo modo tendo ad abbassare gli inevitabili errori.
Così, almeno per me, è tutto più chiaro.
Io uso questo metodo:
Chiamo la funzione di interrogazione che:
Crea la stringa di interrogazione
Apre il database
Esegue l'interrogazione passandola a una stringa o ad un vettore
Chiude il database
Ritorna il/i risultati alla routine chiamante.

Ma a parte questo io non ho capito ma le date nella tabella movimgg sono di tipo Integer?

La mia lettura scansionata della tabella  movimgg (quella incriminata, contenente oltre 54000 record) è fatta attraverso il seguente codice

Una scansione su una colonna TEXT non indicizzata di circa 60.000 dati SQLite la fa in circa 10 millesimi di secondo, quindi vedi tu...

Sappimi dire quanto ci metti a scansionare una data col mio programma sul tuo computer io come massimo ci ho messo 2 secondi ma normalmente meno di uno.

Indicizzando la colonna ottengo miglioramenti insignificanti (1 o 2 centesimi di secondo e parliamo di 200.000 record).

[Gianluigi]

Buongiorno a tutti.
...
Chi mi vuole dare una mano ad avviare uno studio sulla riorganizzazione del DB?

Perché non azzeriamo tutto quanto detto e ti decidi a spostare il tuo progetto su un altro database?
C'è MySQL (tornu docet) ma se vuoi c'è anche il lascito di Sotema (la guida all'installazione e uso di Postgresql).
Lo so è un azardo ma qui siamo di fronte a un database già collaudato e funzionte.
Sono tantissimi i programmi che per riorganizzarsi si aggiornano completamente...
Cosa te ne pare?

[Picavbg]

non ho capito ma le date nella tabella movimgg sono di tipo Integer?

Si,  è una colonna definita "INTEGER"

Sappimi dire quanto ci metti a scansionare una data col mio programma sul tuo computer io come massimo ci ho messo 2 secondi ma normalmente meno di uno.

Scusa, ma non ho capito come posso fare. Il tuo programma punta a un DB che io non possiedo e pertanto mi risponde "NESSUN DATO TROVATO"

[Picavbg]

Perché non azzeriamo tutto quanto detto e ti decidi a spostare il tuo progetto su un altro database?
C'è MySQL (tornu docet) ma se vuoi c'è anche il lascito di Sotema (la guida all'installazione e uso di Postgresql).
Lo so è un azardo ma qui siamo di fronte a un database già collaudato e funzionte.
Cosa te ne pare?

Mi fa semplicemente paura. mi sento già la febbre addosso. 

Sono tantissimi i programmi che per riorganizzarsi si aggiornano completamente...

Significa riscrivere tutti i passi di programma che puntano al DB e, poi, migrare il DB attuale sul nuovo, non dopo però averlo studiato.
Io non sono così studioso come  te, Tornu e gli altri bravi amici del Forum, ma ricordo che le strutture di Mysql e Postgresql sono consigliate nella gestione di DB in una rete di pc connessa ad un server.
É  stato questo il motivo per cui, all'epoca, l'unico tipo di DB rimasto da impiegare in Gambas fu Sqlite.

[Gianluigi]

non ho capito ma le date nella tabella movimgg sono di tipo Integer?

Si,  è una colonna definita "INTEGER"

Io non sapevo che avessi usato Integer e la prova l'ho fatta con Text.

Sappimi dire quanto ci metti a scansionare una data col mio programma sul tuo computer io come massimo ci ho messo 2 secondi ma normalmente meno di uno.

Scusa, ma non ho capito come posso fare. Il tuo programma punta a un DB che io non possiedo e pertanto mi risponde "NESSUN DATO TROVATO"

Il programma allegato crea alla partenza il database nella tua home e se c'è errore te lo dovrebbe segnalare.
Per sicurezza l'ho scaricato e provato in un vecchio PC ci mette un po ma funziona.
Avvialo e dagli il tempo di creare e inserire nella tabella user 200.001 records

P.S. Se ti risponde "Nessun Dato Trovato" vuol dire che si è creato il database controlla in Hone/NomeProgramma/database
Potrebbe non essere riuscito a creare i record prova a cancellare la cartella in home e a riavviare e poi attendi che appaia la finestra.
Potrebbe anche essere successo che tu hai beccato un arco di tempo che non esiste lui crea date fra il 2009 e il 2018 non oltre il 28 di ogni mese (non avevo voglia di lavorare troppo) 

[Gianluigi]

Mi fa semplicemente paura. mi sento già la febbre addosso. 

Significa riscrivere tutti i passi di programma che puntano al DB e, poi, migrare il DB attuale sul nuovo, non dopo però averlo studiato.
Io non sono così studioso come  te, Tornu e gli altri bravi amici del Forum, ma ricordo che le strutture di Mysql e Postgresql sono consigliate nella gestione di DB in una rete di pc connessa ad un server.
É  stato questo il motivo per cui, all'epoca, l'unico tipo di DB rimasto da impiegare in Gambas fu Sqlite.

Come già ci hanno spiegato in tanti, questi database possono essere usati anche nel nostro computer un po come SQLite.
Invece che collegarti al database sul server esterno ti colleghi in localhost.
Se si usa Postgresql, per quel poco che ho fatto ti assicuro che le interrogazioni sono simili a quelle di SQLite.
SQL rimane sempre quello.