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

[Pietro Battiston]

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.

Ora però mi trovo nella seguente situazione:

1a) uso una query con l'ORM di sqlalchemy per recuperare un oggetto
mappato alla tabella A in base alla sua id: ci mette un paio di
centesimi di secondi,

1b) faccio un "print(oggetto_recuperato.rel)": mi si pianta lì
(sicuramente per almeno una buona mezz'ora, non ho aspettato oltre).

L'utilizzo della CPU e della RAM è basso, il bottleneck è chiaramente il
disco, che sembra fare un sacco di seek (iotop non mostra neanche chissà
che flusso di dati). Il database è sì frammentato (~200 extents), ma
niente di mortale direi date le dimensioni del file.

Mentre invece se non uso sqlalchemy, ma direttamente sqlite3 (con _lo
stesso elemento_):

2a) faccio una "SELECT * from a where id=?", la_mia_id: ci mette un
millesimo di secondo circa

2b) faccio una "SELECT * from arel where id1=?", la_mia_id: ci mette un
secondo circa,

2c) per ogni riga trovata in 2b), faccio una "SELECT * from a where
id=?", riga[1]: ci mette qualche centesimo di secondo per elemento: in
totale, circa un decimo di secondo.

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

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?!

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

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.

Grazie mille di qualsiasi consiglio,

Pietro