On 1/31/2015 12:21 AM, Godmar Back wrote:
If I want to do a dump of the table, as in "SELECT value FROM ...." I'll
quickly get "database is locked" errors. Googling revealed that those are
because a long running select keeps a cursor, and thus readlock on the
entire database. I have since rewritten the query using multiple SELECT *
FROM ... LIMIT a, b where b = 50 and a = 0, 50, 100, 150, ..... However,
it takes 20 hours to fully extract the table's 850,000 records, with only
minimal per record processing.
That's a quadratic algorithm you have here. In order to start reading
from the b-th record, SQLite needs to read and discard the previous b
records. You would be better off with a "WHERE id > $LastID ORDER BY id
" clause, where $LastID would be the key of the last record in the
previous chunk.
Better still, switch to WAL mode ( https://www.sqlite.org/wal.html ) It
allows readers to work simultaneously with a single writer. Then just
run a single query across the whole table.
My question: is this performance expected, or am I doing something wrong?
Is there a quick way to count and/or dump an approximate snapshot of a
single 2-column table such as the one shown above, even while keeping the
database available for concurrent writes?
Do you ever delete records from this table? If you don't, you can get a
count with
select max(rowid) from mytable;
That should be nearly instantaneous. If you do delete rows, and all you
need is a quick count, then I would suggest adding an ON INSERT and ON
DELETE triggers that would update a count stored in a separate,
singleton table.
I'm not sure what you mean by "dump an approximate snapshot".
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
