Hello Domingo,
DAD>db.exec_dml("PRAGMA synchronous = 0;");
DAD>db.exec_dml("PRAGMA journal_mode = WAL");
DAD>//db.exec_dml("PRAGMA journal_mode = MEMORY;");
DAD>//db.exec_dml("PRAGMA journal_mode = OFF;");
DAD>//db.exec_dml("PRAGMA locking_mode = EXCLUSIVE;");
DAD>db.exec_dml("PRAGMA temp_store = MEMORY;");
DAD>//db.exec_dml("PRAGMA threads = 4;");
Here are some of the pragma's you reported this morning. Any place
you have "MEMORY" can consume most of your memory when doing a bunch of
transactions.
I'm going to guess you're still using some of these "use memory"
pragma's.
Sunday, October 2, 2016, 4:35:23 PM, you wrote:
DAD> Hello !
DAD> I'm still fighting to use sqlite with a 20GB database and now I'm trying
DAD> to update a table with 980M rows but sqlite is eating all my memory
DAD> (2GB) and making blocking my computer (too much swap).
DAD> I'm in doubt if sqlite can really manage databases above 1GB.
DAD> Have someone any experience with big databases ?
DAD> Cheers !
DAD> The query:
DAD> --explain query plan
DAD> update bolsas_familia set favorecido_id=(select id from favorecidos
DAD> where nis=favorecido_id);
DAD> Query plan:
DAD> selectid|order|from|detail
DAD> 0|0|0|SCAN TABLE bolsas_familia
DAD> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
DAD> 0|0|0|SEARCH TABLE favorecidos USING COVERING INDEX favorecidos_nis_idx
DAD> (nis=?)
DAD> The schema is:
DAD> CREATE TABLE bolsas_familia(
DAD> id integer primary key,
DAD> favorecido_id integer not null,
DAD> valor_parcela numeric,
DAD> mes_competencia integer
DAD> ); //980M rows
DAD> CREATE TABLE "favorecidos"(
DAD> id integer primary key,
DAD> nis integer,
DAD> nome varchar collate nocase_slna,
DAD> municipio_id integer,
DAD> programa_id integer not null,
DAD> fonte_finalidade_id integer not null,
DAD> first_ano_mes integer
DAD> ); //14M rows
DAD> CREATE UNIQUE INDEX favorecidos_nis_idx ON favorecidos(nis);
DAD> Cheers !
DAD> _______________________________________________
DAD> sqlite-users mailing list
DAD> [email protected]
DAD> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Teg mailto:[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
