> 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
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
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
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 -
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
> > 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
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()
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
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
> 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
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
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
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
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' >
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
>
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,
> >
> -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 pre
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
18 matches
Mail list logo
