a bit, in my experience.
Do 'PRAGMA page_size = 8192' before you create the database.
Doing both may cause excessive memory use (20 * 8K = ...). I've
never tried that.
Gé
Maksim Yevmenkin wrote:
Robert,
[snip]
i said i print these rows to /dev/null too in my
Thomas,
with sum(n1) added query runs twice as slow. as i was told its because
sqlite has to fetch data row. fine, but why its soo slow?! and it
Because for each row it has to compute the aggregate key, find the
aggregator for that key and increment the sum for that aggregate key.
SELECT count(*) FROM (SELECT a,n1 FROM data WHERE a = 18234721 GROUP BY a);
time sqlite3 db test.sql
30
1024
1417
13.14u 1.06s 0:14.40 98.6%
Have you tried doing the query like this:
SELECT count(*) FROM (SELECT a,nl FROM data WHERE a-18234721=0 GROUP
BY a);
i just
John.
i think, i know what is going on here. the problem is that every time
i do an indexed scan sqlite has to
1) fetch index pages
2) fetch data pages that match where condition
because both index and data are in the same file sqlite has to perform
insane amount of seek() calls and
Christian,
thanks for the reply.
i'm having strange performance problem with sqlite-3.2.0. consider the
following table
[snip]
now the problem:
1) if i do a select with an idex it takes 27 sec. to get 92 rows
time sqlite3 db 'select n2 from data where a = 18234721' /dev/null
Robert,
i guess, i can believe this. however its pretty disappointing to get
50% improvement on 30 times less dataset :(
but how do you explain this?
sqlite .schema data
CREATE TABLE data
(
a INTEGER,
b INTEGER,
c CHAR,
d INTEGER,
e
Robert,
time sqlite3 db 'select n1 from data where a = 18234721' /dev/null
26.15u 0.59s 0:27.00 99.0%
time sqlite3 db 'select n1 from data where a = 18234721' /dev/null
26.04u 0.61s 0:26.91 99.0%
time sqlite3 db 'select e from data where a = 18234721' /dev/null
12.22u 0.41s
Robert,
[snip]
i said i print these rows to /dev/null too in my perl code. plus the
perl code does some other things such as joining these rows with other
hashes and summing the numbers.
That's fine. I was merely trying to account for the 50% speed difference
between the two
