> -----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% 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 INTEGER, > n1 FLOAT, > n2 FLOAT > ); > CREATE INDEX data_by_a ON data (a); > > > 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% > > and > > > time sqlite3 db 'select e from data where a <= 18234721' > /dev/null > 12.22u 0.41s 0:12.67 99.6% > > time sqlite3 db 'select e from data where a <= 18234721' > /dev/null > 12.01u 0.61s 0:12.64 99.8% > > so just by selecting two different rows (both next to each other). i > get another 50% time difference? sure the column types are different, > and i can even understand that FLOAT might be 8 bytes and INTEGER 4 > bytes, but 50% time difference? it just cant be that linear can it? > > do you think it is possible to get better results by issuing multiple > queries each of which will return even less dataset? and, no, i'm not > a database guy.
The most glaring fault in your time tests that I see is that you're running the command-line sqlite3, which is printf'ing all 339,000+ rows to dev/null. It doesn't show on your screen, but the work is still being done regardless. If you really want a fair speed test, change those statements to: select count(n1) from data where a <= 18234721 And select count(e) from data where a <= 18234721 Or even select count(*) from data where a <= 18234721 THEN tell us what the difference in performance is ... Robert
