Insert update sqlite lento

Umberto_Romano · January 2, 2018, 8:58pm

Salve, leggo da un database postgres 2000 record e scrivo su sqlite effettuando 133 inserimenti e circa 1700 update. Nonostante la query sia rapidissima per effettuare le operazioni su sqlite il sistema impiega 40 minuti. Come posso ovviare? La mia e unapplicazione desktop.
Grazie

Raul_Juarez_Pulache · January 3, 2018, 2:10am

Hello Umberto Romano

You have created an index for the table, using as fields the WHERE columns that you use in the Update

Cordially,

Raul

Antonio_Rinaldi · January 3, 2018, 6:56am

Ciao Umberto,
come ha detto Raul probabilmente hai gli indici non corretti (vale a dire quelli che usi per le where non sono gli stessi che hai creato)

Alternativamente puoi fare l’intera operazione in una transazione o utilizzare una tabella d’appoggio (magari su un db in memory) e poi scrivi i dati (per insert o per update) in un unico comando.

Umberto_Romano · January 3, 2018, 8:17am

Ciao,
l’update usa l’unico indicedella tabella ed ho provato anche utilizzando Begin Transaction, ma i tempi sono gli stessi. Come si utilizza un db memory?

Umberto_Romano · January 3, 2018, 12:23pm

Ho fatto l’explain plan dell’update ed ottengo : USING COVERING INDEX uk_annibudget. Nella where ci sono tutti i campi dell’indice e nel set i campi che devo aggiornare.

Antonio_Rinaldi · January 3, 2018, 12:27pm

Un db sqlite in memory semplicemente un db a cui non assegni il file e ti connetti direttamente.
Ma ricordati che in memoria per cui se poi non trasferisci il contenuto su disco, quando lo chiudi perderai tutto il contenuto.
In compenso velocissimo.

Il begin Transaction lo fai prima di tutte le operazioni o per ogni operazione? In genere dovrebbe fare una notevole differenza.
In ogni caso con il db in memoria ( la tecnica che uso io ormai):

crei nel db in memoria una tabella strutturalmente identica a quella in cui vuoi scrivere i dati + un campo (ad esempio esiste as integer default 0) che ti servir per decidere se dovrai fare un update o un insert
inerisci tutti i record in questa tabella
fai l’attach al db fisico (a cui dai un nome ad esempio ORIGINE)
con un comando unico marchi tutti i record che esistono (update tabella set esiste=1 where <condizioni verificano ORIGINE.condizioni)
-con un comando unico inserisci in ORIGINE tutti i record che hanno esiste=0
-con un comando unico aggiorni in ORIGINE tutti i record che hanno esiste=1
-fai il detach di ORIGINE
Finito

In questo modo importo i dati molto velocemente, dipende solo dai tempi di lettura dei dati dal db o dal file da cui stai copiando

Umberto_Romano · January 4, 2018, 3:42pm

La transaction la faccio all’inizio di tutte le operazioni e la chiudo al termine.
Ho creato il db memory, in realt ho una struttura particolare fatta in questo modo:

+"importo_budgen double,"_ +"importo_budfeb double,"_ +"importo_budmar double,"_ +"importo_budapr double,"_ +"importo_budmag double,"_ +"importo_budgiu double,"_ +"importo_budlug double,"_ +"importo_budago double,"_ +"importo_budsett double,"_ +"importo_budott double,"_ +"importo_budnov double,"_ +"importo_buddic double,"_ +"importo_tot_reparto double, "_ +"importo_tot_gruppo double, "_ +"importo_tot_codice double, "_ +"importo_totale double, "_ +"codice text,descrizione text,"_
ogni record letto dal db postgres per mese ed inserisco una riga per il primo importo, poi vado in update per i mesi successivi.
Con il db memory impiego comunque 12 minuti per faccio 133 insert e 1500 update come facevo con il db fisico. Cosa devo fare per migliorare la prestazione?
Grazie

Antonio_Rinaldi · January 4, 2018, 3:47pm

Mi scrivi una dei comandi di insert e uno dei comandi di update che vai ad generare/utilizzare?

Io carico centinaia di migliaia di record decisamente in tempi più brevi… ci deve essere qualcosa che non torna nel tuo approccio…

Umberto_Romano · January 4, 2018, 3:54pm

Tabella:

[quote]dbMemory.SQLExecute(“Create table budgetdettaglio (anno text,versione text,mansione text,filiale text,”_
+“gruppomansioni text,reparto text,tipo_imp text,ordine integer,”_
+“importo_budgen double,”_
+“importo_budfeb double,”_
+“importo_budmar double,”_
+“importo_budapr double,”_
+“importo_budmag double,”_
+“importo_budgiu double,”_
+“importo_budlug double,”_
+“importo_budago double,”_
+“importo_budsett double,”_
+“importo_budott double,”_
+“importo_budnov double,”_
+“importo_buddic double,”_
+"importo_tot_reparto double, "_
+"importo_tot_gruppo double, "_
+"importo_tot_codice double, "_
+“importo_totale double, “_
+“codice text,descrizione text,”_
+” PRIMARY KEY (anno,tipo_imp,versione,filiale,reparto))”)[/quote]

insert

[quote]Dim row As New DatabaseRecord
if importo_bud > 0 then
row.Column(“anno”) = anno
row.Column(“versione”) = versione
row.Column(“mansione”) = mansione
row.Column(“filiale”) = filialedesc
row.Column(“gruppomansioni”) = gruppomansioni
row.Column(“reparto”) = reparto
row.Column(“tipo_imp”) = tipo_imp
// ricavo codicevoce
rs=rst(“select codicevoce from vocidettaglio where codice=”+sapici(ordine)+" and descrizione like “+sapici(”%"+descrizione+"%") +" and anno="+sapici(anno)_
+" and idfiliale="+sapici(str(filiale))+" and versione="+sapici(versione))
while not rs.EOF
ordine=rs.IdxField(1).StringValue
rs.MoveNext
wend
rs.Close

// ricavo voce e descrizione totale
rs=rst(“select codice,descrizione from voci where codice=”+sapici(ordine.Mid(1,5)) +" and anno="+sapici(anno)_
+" and idfiliale="+sapici(str(filiale))+" and versione="+sapici(versione))

while not rs.EOF
codice=rs.IdxField(1).StringValue
vocedesc=rs.IdxField(2).StringValue
rs.MoveNext
wend
rs.Close

row.Column(“ordine”) = ordine
row.Column(“codice”) = codice
row.Column(“descrizione”) = vocedesc
if mese=“1” then
importo_budgen=importo_bud*-1
row.Column(“importo_budgen”) = Str(importo_bud*-1)
end if
if mese=“2” then
importo_budfeb=importo_bud*-1
row.Column(“importo_budfeb”) = Str(importo_bud*-1)
end if
if mese=“3” then
importo_budmar=importo_bud*-1
row.Column(“importo_budmar”) = Str(importo_bud*-1)
end if
if mese=“4” then
importo_budapr=importo_bud*-1
row.Column(“importo_budapr”) = Str(importo_bud*-1)
end if
if mese=“5” then
importo_budmag=importo_bud*-1
row.Column(“importo_budmag”) = Str(importo_bud*-1)
end if
if mese=“6” then
importo_budgiu=importo_bud*-1
row.Column(“importo_budgiu”) = Str(importo_bud*-1)
end if
if mese=“7” then
importo_budlug=importo_bud*-1
row.Column(“importo_budlug”) = Str(importo_bud*-1)
end if
if mese=“8” then
importo_budago=importo_bud*-1
row.Column(“importo_budago”) = Str(importo_bud)
end if
if mese=“9” then
importo_budsett=importo_bud*-1
row.Column(“importo_budsett”) = Str(importo_bud*-1)
end if
if mese=“10” then
importo_budott=importo_bud*-1
row.Column(“importo_budott”) = Str(importo_bud*-1)
end if
if mese=“11” then
importo_budnov=importo_bud*-1
row.Column(“importo_budnov”) = Str(importo_bud*-1)
end if
if mese=“12” then
importo_buddic=importo_bud*-1
row.Column(“importo_buddic”) = Str(importo_bud*-1)
end if
if mese=“13” then
importo_reale=importo_bud*-1
row.Column(“importo_reale”) = Str(importo_reale*-1)
end if

dbmemory.InsertRecord(“budgetdettaglio”, row)
[/quote]

Update

[quote] if dbmemory.Error then
dbmemory.SQLExecute(“update budgetdettaglio “_
+“set importo_budgen = (case when importo_budgen is null then 0 else importo_budgen end)+”+sapici(str(importo_budgen))+”,”_
+" importo_budfeb = (case when importo_budfeb is null then 0 else importo_budfeb end) +"+sapici(str(importo_budfeb))+","_
+" importo_budmar = (case when importo_budmar is null then 0 else importo_budmar end)+"+sapici(str(importo_budmar))+","_
+" importo_budapr = (case when importo_budapr is null then 0 else importo_budapr end)+"+sapici(str(importo_budapr))+","_
+" importo_budmag = (case when importo_budmag is null then 0 else importo_budmag end)+"+sapici(str(importo_budmag))+","_
+" importo_budgiu = (case when importo_budgiu is null then 0 else importo_budgiu end)+"+sapici(str(importo_budgiu))+","_
+" importo_budlug = (case when importo_budlug is null then 0 else importo_budlug end)+"+sapici(str(importo_budlug))+","_
+" importo_budago = (case when importo_budago is null then 0 else importo_budago end)+"+sapici(str(importo_budago))+","_
+" importo_budsett = (case when importo_budsett is null then 0 else importo_budsett end)+"+sapici(str(importo_budsett))+","_
+" importo_budott = (case when importo_budott is null then 0 else importo_budott end)+"+sapici(str(importo_budott))+","_
+" importo_budnov = (case when importo_budnov is null then 0 else importo_budnov end)+"+sapici(str(importo_budnov))+","_
+" importo_buddic = (case when importo_buddic is null then 0 else importo_buddic end)+"+sapici(str(importo_buddic))+" “_
+” where anno="+sapici(anno)+" and tipo_imp="+sapici(tipo_imp)+" and versione="+Sapici(versione)+" and filiale="+sapici(filialedesc)+" and reparto="+sapici(reparto))
if dbmemory.Error then
MsgBox("Errore insrimento o aggiornamento per stampa "+dbmemory.ErrorMessage)
dbmemory.Rollback
else
//dbsetting.Commit
end if

end if[/quote]

Antonio_Rinaldi · January 4, 2018, 7:05pm

Per quanto si possa decisamente migliorare a query di update(*) e magari utilizzare i prepared statement per ulteriori ottimizzazioni, io vedo i seguenti problemi: (ma vedendone una parte magari mi sfugge qualcosa della logica)

Vedo due while in cui cicli per arrivare in fondo: o hai un solo record sempre e quindi il while non serve o se ne puoi avere più di uno ma vuoi solo l’ultimo fai la query ordinandola al contrario e limita la risposta ad un record

2)Mese lo controlli per tutti i valori ma (credo) tu ne abbia uno solo alla volta per cui un select case ti permette di non verificare sempre tutti i casi ma uscire la ciclo prima

2a)Per lo stesso motivo potresti provare ad aggiornare solo il campo interessato

(*) invece di
importo_budXXX = (case when importo_budXXX is null then 0 else importo_budXXX end) + YYY
puoi usare
importo_budXXX = ifnull(importo_budXXX, 0)+ YYY
Accorci la stringa rendi più veloce la sua costruzione

Con un prepared statement (almeno lato sqlite) la costruisci e prepari un volta e poi la usi come vuoi senza richiamare n volte sapici
dim p as sqlitePreparedStatement=dbmemory.prepare("UPDATE budgetdettaglio SET importo_budgen=if(importo_budgen, 0)+ ?, importo_budfeb=ifnull(importo_budfeb, 0)+?, where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?)
p.bindType(0, SQLitePreparedStatement.SQLITE_DOUBLE)
p.bindType(1, SQLitePreparedStatement.SQLITE_DOUBLE)
…
p.bindType(12, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(13, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(14, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(15, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(16, SQLitePreparedStatement.SQLITE_TEXT)
…
p.sqlExecute importo_budgen, importo_budfeb, , anno, tipo_imp, versione, filialedesc, reparto

Se poi puoi determinare il mese come detto in 2a puoi creare 12 prepared statement (uno per mese) e usare solo quello che ti interessa in quel momento (li prepari una volta li fai eseguire quando ti servono)

Come ultimo consiglio puoi provare ad usare microseconds per vedere il costo di ogni “sezione” dell’operazione e farlo scrivere nel debuglog in questo modo puoi identificare dove perdi tempo in realtà e potresti avere delle sorprese

Umberto_Romano · January 5, 2018, 6:10pm

Ho fatto in questo modo

[quote] row.Column(“ordine”) = ordine
row.Column(“codice”) = codice
row.Column(“descrizione”) = vocedesc
select case mese
case “1”
row.Column(“importo_budgen”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budgen=(case when importo_budgen is null then 0 else importo_budgen end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)
case"2"
row.Column(“importo_budfeb”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budfeb=(case when importo_budfeb is null then 0 else importo_budfeb end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case"3"
row.Column(“importo_budmar”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budmar=(case when importo_budmar is null then 0 else importo_budmar end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “4”
row.Column(“importo_budapr”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budapr=(case when importo_budapr is null then 0 else importo_budapr end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “5”
row.Column(“importo_budmag”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budmag=(case when importo_budmag is null then 0 else importo_budmag end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “6”
row.Column(“importo_budgiu”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budgiu=(case when importo_budgiu is null then 0 else importo_budgiu end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “7”
row.Column(“importo_budlug”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budlug=(case when importo_budlug is null then 0 else importo_budlug end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “8”
row.Column(“importo_budago”) = Str(importo_bud)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budago=(case when importo_budago is null then 0 else importo_budago end) + ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “9”
row.Column(“importo_budsett”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budsett=(case when importo_budsett is null then 0 else importo_budsett end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “10”
row.Column(“importo_budott”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budott=(case when importo_budott is null then 0 else importo_budott end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “11”
row.Column(“importo_budnov”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_budnov=(case when importo_budnov is null then 0 else importo_budnov end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

case “12”
row.Column(“importo_buddic”) = Str(importo_bud*-1)
p=dbmemory.prepare(“UPDATE budgetdettaglio SET importo_buddic=(case when importo_buddic is null then 0 else importo_buddic end)+ ? “_
+” where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?”)

end select

dbmemory.InsertRecord(“budgetdettaglio”, row)

if dbmemory.Error then
p.bindType(0, SQLitePreparedStatement.SQLITE_DOUBLE)
p.bindType(1, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(2, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(3, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(4, SQLitePreparedStatement.SQLITE_TEXT)
p.bindType(5, SQLitePreparedStatement.SQLITE_TEXT)

Importo=importo_bud*-1

p.sqlExecute importo, anno, tipo_imp, versione, filialedesc, reparto
  if dbmemory.Error then
  app.testoerrori="Errore insrimento o aggiornamento per stampa "+dbmemory.ErrorMessage
  dbmemory.Rollback

end if

[/quote]

Per i tempi sono gli stessi, se metto il System.debuglog dove vedo i risultati sul mac?

Antonio_Rinaldi · January 5, 2018, 6:45pm

I log li vedi direttamente in Xojo premendo il terzo pulsante in basso nell’IDE (quello con il simbolo RSS)
potresti fare cos per visualizzare i tempi:

[code]dim t0, t1 as double
t0=microseconds

row.Column(“ordine”) = ordine
row.Column(“codice”) = codice
row.Column(“descrizione”) = vocedesc

t1=microseconds

select case mese
case “1”…

end select
t1=microseconds-t1
system.debugLog "Prepara dati: "+t1.toText

t1=microseconds
dbmemory.InsertRecord(“budgetdettaglio”, row)
t1=microseconds-t1
system.debugLog "Tentativo inserimento: "+t1.toText

if dbmemory.Error then
…
t1=microSeconds
p.sqlExecute importo, anno, tipo_imp, versione, filialedesc, reparto
t1=microSeconds-t1
system.debugLog "Aggiornamento: "+t1.toText

if dbmemory.Error then
app.testoerrori="Errore insrimento o aggiornamento per stampa "+dbmemory.ErrorMessage
dbmemory.Rollback

end if
t0=microseconds-t0
system.debugLog "Totale operazione: "+t0.toText
[/code]

In questo modo hai tempi per un ciclo.
La sequenza si ripeter per tutti i record del ciclo (hai detto 2000 record)

in alternativa (o in aggiunta)
Potresti creare come prepared statement anche l’insert con INSERT ON CONFLICT IGNORE into table xxx (campi) values (?, ?, ?..)
e questo con i soli campi non dei budget in modo da eseguire sempre l’update

Fuori del ciclo (prima di iniziare la procedura) potresti preparare i 12 prepared statement con un semplice ciclo: (scusa gli errori di sintassi sto scrivendo direttamente qui)

dim mesi=array("gen", "feb", ...., "dic") dim pMesi() as preparedStatement for i as integer=0 to mesi.ubound pMesi.append dbmemory.prepare("UPDATE budgetdettaglio SET importo_bud"+mesi(i)+"=ifnull(importo_bud"+mesi(i)+", 0)+ ? "_ +" where anno=? and tipo_imp=? and versione=? and filiale=? and reparto=?") pMesi(pMesi.ubound).bindType(0, SQLitePreparedStatement.SQLITE_DOUBLE) pMesi(pMesi.ubound).bindType(1, SQLitePreparedStatement.SQLITE_TEXT) pMesi(pMesi.ubound).bindType(2, SQLitePreparedStatement.SQLITE_TEXT) pMesi(pMesi.ubound).bindType(3, SQLitePreparedStatement.SQLITE_TEXT) pMesi(pMesi.ubound).bindType(4, SQLitePreparedStatement.SQLITE_TEXT) pMesi(pMesi.ubound).bindType(5, SQLitePreparedStatement.SQLITE_TEXT) next

A questo punto la select case ti si semplifica in:

select case mese
  case "1"
    row.Column("importo_budgen") = Str(importo_bud*-1)
    p=pMesi(0)
  case"2" 
    row.Column("importo_budfeb") = Str(importo_bud*-1)
    p=pMesi(1)
...
  case "12" 
    row.Column("importo_buddic") = Str(importo_bud*-1)
    p=pMesi(11)
end select

e non devi neanche rifare i bind

Dovendo richiamare le cose per 2000 record ogni tempo risparmiato utile

Umberto_Romano · January 6, 2018, 11:22am

Il problema sono le 2 query:

[quote] // ricavo codicevoce
rs=rst(“select codicevoce from vocidettaglio where codice=”+sapici(ordine)+" and descrizione like “+sapici(”%"+descrizione+"%") +" and anno="+sapici(anno)_
+" and idfiliale="+sapici(str(filiale))+" and versione="+sapici(versione)+" and tipo=‘AC’")
if rs.RecordCount>0 then
rs.MoveFirst
//while not rs.EOF
ordine=rs.IdxField(1).StringValue
//rs.MoveNext
//wend

end if
rs.Close

//ricavo voce e descrizione totale
rs=rst(“select codice,descrizione from voci where codice=”+sapici(ordine.Mid(1,5)) +" and anno="+sapici(anno)_
+" and idfiliale="+sapici(str(filiale))+" and versione="+sapici(versione)+" and tipo=‘AC’")

//if rs.RecordCount>0 then
//rs.MoveFirst
codice=rs.IdxField(1).StringValue
vocedesc=rs.IdxField(2).StringValue
//rs.MoveNext
//wend
//end if
rs.Close
[/quote]

La stranezza che inizia veloce e poi rallenta sensibilmente con il passare del tempo nonostante entrambe le query estraggono un solo dato.

Antonio_Rinaldi · January 7, 2018, 9:26am

Immaginavo che il problema fosse a livello di quelle query.

Anche qui valgono le stesse raccomandazioni:
Gli indici sono corretti? (il fatto che rallenti progressivamente mi fa pensare di no)
Puoi utilizzare un preparedStatement?
controlla solo if not rs.eof per verificare se c’ il dato, se c’ non necessario muoversi (come fai per la seconda query) altrimenti puoi saltare il ciclo (credo)
Puoi provare a creare una try unica (con left join) per evitare 2 query?

Se, come credo, il tutto nasce da una query generale: puoi avere un recordset con tutti i dati (con left join multipli) in modo da averne una unica con tutti i dati e evitare del tutto le subquery?

Umberto_Romano · January 7, 2018, 11:59am

Ho fatto la left join ottenendo una sola query , purtroppo non andr mai per indice perch ha una like, la query restituisce sempre un record e legge da postgres. Ho pensato di metterla a livello della query generale, ma in quel punto mi mancano dei dati.Unalternativa e fare questa query esterna , cio richiamarla prima di chiamare il metodo di insert e update, non so se cos guadagno qualcosa visto che la query verr richiamata lo stesso numero di volte. Altra soluzione eseguire la query principale passando la descrizione che vedi nella subquery che ho creato, ma questo comporta che la query principale deve essere chiamata almeno 8 volte(tante sono le descrizioni).
Non so cosa mi consigli?
Grazie

Antonio_Rinaldi · January 7, 2018, 12:21pm

Ecco. L’interrogazione con like pu abbattere le prestazioni.
Ma dalla query con like non potresti identificare dei dati da riutilizzare poi in query indicizzate?

Se, come dici, sono solo 8 le descrizioni, potresti raccogliere solo questi dati (non so come sono strutturate le tabelle)

Umberto_Romano · January 7, 2018, 6:01pm

Grazie Antonio per le tutte le dritte dal preparedstatement, al debuglog e all’ultimo consiglio. Ho fatto la query principale con 10 left join (una per ogni descrizione) in modo da ottenere tutto in un resultset, ora impiega 5 secondi.