Re: [sqlite] expected performance of selecting large numbers of records

2015-01-31 Thread Godmar Back
Thank you very much for those suggestions!

Select max(rowid) completes immediately, I haven't tried WAL mode and the
idea of sorting yet. appears to be down right now, but is up.

On Sat, Jan 31, 2015 at 5:31 AM, Igor Tandetnik  wrote:

> I'm not sure what you mean by "dump an approximate snapshot".

I meant that I don't care whether records that were recently added, say
during the last few seconds, are included in the total count()/dump of all
records or not. So I'm fine with an approximate snapshot that may not have
a few of the latest updates.

 - Godmar
sqlite-users mailing list

[sqlite] expected performance of selecting large numbers of records

2015-01-30 Thread Godmar Back
I have a single SQLite 2-column table with a primary TEXT key and a value,
like so (id TEXT PRIMARY KEY, value TEXT). One process adds new records
periodically, perhaps 1-10 per minute. The database currently has 850,000
entries and is 7.3GB large on disk.

I also need to perform bulk queries, which appear to be very slow. On an
average desktop PC, for instance, a "SELECT COUNT(*)" takes over 5 minutes.

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.

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?

I'm using Ubuntu 12.10 with 3.7.9-2ubuntu1.1 libsqlite3 using the Python

 - Godmar
sqlite-users mailing list