[Python] O non capisco sqlite, o non capisco sqlalchemy, o entrambi

[Manlio Perillo]

On 16/11/2013 18:57, Pietro Battiston wrote:
> Salve a tutti,
>
> ho un database piuttosto semplice:
>
> tabella A, 2 colonne String di cui una "id", ed un'altra colonna "col",
> tabella accessoria AREL, per una many-to-many tra A e A, che chiameremo
> "rel": colonne "id1" e "id2"
> Altre due tabelle che non c'entrano niente (credo) in questa storia.
>
> Ogni tabella ha tra il milione ed i 5 milioni di righe, i valori sono
> più o meno tutti tra i 10 e i 30 caratteri. In tutto un database sqlite
> da circa 400 MB. Sqlite fa schifo? OK OK, non avrei problemi a migrare a
> qualcosa di serio... se il problema fosse sqlite.
>
> Ora, io di norma non tocco un database se non tramite sqlalchemy. Fingo
> che sia perché mi piace scrivere codice portabile/elegante - la verità è
> che fino a ieri non avevo mai scritto una query SQL.
>

Male, anzi malissimo.
Invece di imparare ad usare una libreria, specialmente una cosa 
complessa come l'ORM di SQLAlchemy, ti consiglio di imparare l'SQL.

> [...]
> Ora, io capisco che l'ORM possa essere un po' più lento. Ma qui c'è una
> differenza impressionante.
>

Non è colpa dell'ORM.

> Per capire, mi sono andato a vedere la query che sqlalchemy genera, ed è
> come segue:
>
> "SELECT a.id AS a_id, a.col AS a_col FROM a, arel WHERE ? = arel.id1 AND
> a.id = arel.id2", la_mia_id
>
> Provando a chiamare direttamente questa query direttamente con sqlite,
> in effetti ottengo lo stesso effetto che usando l'ORM di slqalchemy.
>
> Ora, le mie conoscenze/ricerche di SQL sono sufficienti per farmi capire
> che questa è una JOIN implicita, e qual'è la sua logica. Però non
> capisco:
> 1) dal punto di vista implementativo: com'è possibile che una JOIN sia
> così più lenta di svariate SELECT che fanno (concettualmente, per quel
> che ne posso capire) esattamente lo stesso lavoro?!
> 2) ammesso che debba essere così, cosa impedisce a sqlalchemy di usare
> le stesse SELECT che uso io, per recuperare esattamente la stessa roba?!
>

SQLAlchemy non utilizza le select che fai tu, perchè in generale è 
meglio/più efficiente fare un unica query "giusta" che tante piccole query.

> Qualcosa mi suggerisce che in realtà la risposta sia una sola... solo
> che mi sfugge completamente.
>

Nel tuo database hai aggiunto gli indici?
Senza indice, SQLite deve fare una ricerca sequenziale su tutte le 
tuple, per cercare quelle che matchano il criterio di ricerca.  Per una 
join il numero cresce esponenzialmente.  Magari PostgreSQL è in grado i 
ottimizzare la query anche se non usi gli indici, ma senza provare non 
saprei dirti.

Aggiungi gli indice a id, id1 e id2 e sicuramente il tempo impiegato 
sarà più ragionevole.

> Ammesso che c'entri qualcosa, l'argomento "lazy" della relationship non
> lo passo, quindi dovrebbe essere "lazy"... ovvero da quel che capisco
> passando un valore diverso le cose potrebbero solo peggiorare.
>

Semplicemente fa la join da subito, invece che quando serve.
Come detto, facendo meno query dovrebbe essere più efficiente.

Dopo che avrai aggiunto gli indici, prova sia con lazy False che True.

> Grazie mille di qualsiasi consiglio,
>

Un ultimo consiglio è di non usare l'ORM a meno di non aver bisogno 
veramente delle sue funzionalità (ossia in quei casi in cui dovresti 
reimplementarti le query non banali a mano); non è questo il tuo caso, 
quindi usa sqlalchemy.schema e sqlalchemy.sql, che è comunque conveniente.


Ciao  Manlio