On Fri, 8 Apr 2005, Maksim Yevmenkin wrote:
>Hello,
>
>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 333392 rows
>
>> time sqlite3 db 'select n2 from data where a <= 18234721' > /dev/null
>25.95u 0.71s 0:27.02 98.6%
>
>> time sqlite3 db 'select n2 from data where a <= 18234721' > /dev/null
>26.02u 0.66s 0:27.53 96.9%
>
>2) if i do a select with sequential lookup it takes 1min to get 9818210 rows
>
>> time sqlite3 db 'select n2 from data where a+0 <= 18234721' > /dev/null
>49.54u 14.65s 1:04.98 98.7%
>
>> time sqlite3 db 'select n2 from data where a+0 <= 18234721' > /dev/null
>49.80u 14.42s 1:05.03 98.7%
>
>- so how come it take only twice as much time to get 30 times more rows?
When doing an index scan, you will be accessing the index as well as the
table pages. For a sequential scan, only the table pages are touched,
reducing thrashing of the cache.
Also, if the data is not in index order in the table, and/or dispersed
across the database file, you may have to visit each page more than once
when traversing in index order. In the full table scan, you'll read the
rows in table order, hence only touching each page once before moving on
to later pages, thus reducing cache thrashing even more.
>
>- and why is it taking 27 seconds to get 333392 rows anyway?
You think 12347 rows/s is bad?
>
>- is there any way to create an index in separate file?
No, SQLite is a single file embedded database. Keeps administration
almost non-existent (by design.)
>
>the hardware is sun netra t1 running solaris 5.7. the db file size is
>about 800 mbytes.
>
>just for the record i'd like to have at least 15 times more records in
>the 'data' table.
If you can match SQLite for simple read-only throughput with another
database, you'll be doing well.
>
>thanks,
>max
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
