On Tue, 12 Apr 2005, Maksim Yevmenkin wrote:
Dear SQLite users,
consider this
[snip]
it only took 4+ seconds to read, parse, perform hash table lookup and
sum the data. note that for unique 1417 keys it had to do hash lookup
and hash insert.
so, just with plain ascii file i get four times
On Fri, 2005-04-08 at 11:53 -0700, Maksim Yevmenkin wrote:
CREATE INDEX data_by_a ON data (a);
time sqlite3 db 'select n2 from data where a = 18234721' /dev/null
25.95u 0.71s 0:27.02 98.6%
If you make the index look like this:
CREATE INDEX data_by_a ON data(a, n2);
Then SQLite only
well, it could be true, but not in the queries i have posted. i group
by column a and there is an index on column a, so sqlite does not
have to do anything to compute key. it does not even have to back to
Do not confuse the index key with the aggregator key. The two may be
the same in
Maksim,
Some things you could try:
1) increase cache memory
You may be causing a lot of cache misses if the size of the query result
is very large compared to the size of the cache. Index-based searches
can cause multiple reloads of the same page because of a lack of
locality in the cache. An
Gé,
thanks for the suggestion. unfortunately it did not make any
difference :( below is the results. as you can see it takes 7+ seconds
to group by 333,392 records and i'm grouping by column on which i
have index. again, i'm not a database guy, but i think that is slow.
perhaps someone can
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.
That's a lot
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.
On Tue, 2005-04-12 at 16:17 -0700, Maksim Yevmenkin wrote:
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
Maksim Yevmenkin wrote:
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
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
Maksim Yevmenkin wrote:
so, just with plain ascii file i get four times the speed i get with
sqlite. note that my c program will scale linearly with the size of
dataset (just like i see with sqlite).
With anything related to computers, there are always tradeoffs - most
commonly power
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
-Original Message-
From: Maksim Yevmenkin [mailto:[EMAIL PROTECTED]
Sent: Monday, April 11, 2005 9:59 AM
To: Christian Smith
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite performance problem
i guess, i can believe this. however its pretty disappointing to get
50
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
Let's recap ...
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,
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
18 matches
Mail list logo