CODEWITHSUNDEEP
sqlite
ARF
ARF

Reputation: 7684

Sqlite slow but barely using machine ressources

I have a 500MB sqlite database of about 5 million rows with the following schema:

CREATE TABLE my_table (
        id1 VARCHAR(12) NOT NULL,
        id2 VARCHAR(3) NOT NULL,
        date DATE NOT NULL,
        val1 NUMERIC,
        val2 NUMERIC,
        val2 NUMERIC,
        val4 NUMERIC,
        val5 INTEGER,
        PRIMARY KEY (id1, id2, date)
);

I am trying to run:

SELECT count(ROWID) FROM my_table

The query has now been running for several minutes which seems excessive to me. I am aware that sqlite is not optimized for count(*)-type queries.

I could accept this if at least my machine appeared to be hard at work. However, my CPU load hovers somewhere around 0-1%. "Disk Delta Total Bytes" in Process Explorer is about 500.000.

Any idea if this can be sped up?

Upvotes: 3

Views: 99

Answers (3)

AsTeR
AsTeR

Reputation: 7521

I would try to look at my disk IO if I were you. I guess they are quite high. Considering the size of your database some data must be on the disk which makes it the bottleneck.

Two ideas from my rudimentary knowledge of SQLite.

Idea 1: If memory is not a problem in your case and your application is launched once and run several queries, I would try to increase the amount of cache used (there's a cache_size pragma available). After a few googling I found this link about SQLite tweaking: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

Idea 2: I would try to have an autoincremented primary key (on a single column) and try to tweak my request using SELECT COUNT(DISTINCT row_id) FROM my_table; . This could force the counting to be only run on what's contained in the index.

Upvotes: 0

CL.
CL.

Reputation: 180020

In most cases, count(*) would be faster than count(rowid).

If you have a (non-partial) index, computing the row count can be done faster with that because less data needs do be loaded from disk. In this case, the primary key constraint already has created such an index.

Upvotes: 0

subham soni
subham soni

Reputation: 274

You should have an index for any fields you query on like this. create index tags_index on tags(tag);. Then, I am sure definitely the query will be faster. Secondly, try to normalize your table and have a test (without having an index). Compare the results.

Upvotes: 1

Related Questions