Re: [sqlite] sqlite performance problem

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

Re: [sqlite] sqlite performance problem

> > 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

Re: [sqlite] sqlite performance problem

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

Re: [sqlite] sqlite performance problem

', before you run the > query. 1 GB of ram should be able to support 300MB of cache. > > 2) use 8192-byte pages > > Larger pages seem to improve performance quite a bit, in my experience. > > Do 'PRAGMA page_size = 8192' before you create the database. > > Doin

Re: [sqlite] sqlite performance problem

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

Re: [sqlite] sqlite performance problem

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' >

Re: [sqlite] sqlite performance problem

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, > >

Re: [sqlite] sqlite performance problem

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