Hubertus uttered:
Dear list,
sorry to just come up with another performance question. I build a yet small
database with one table. It has about 650.000 rows, 75 columns and
has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The
Laptop runs Suse 10.2 and does basicly nothing but this database.
sqlite3 -version is 3.3.8
This is the schema:
CREATE TABLE 'data'('nr' INTEGER PRIMARY KEY,
'year' INTEGER,
'month' INTEGER,
'day' INTEGER,
'sec' REAL,
'campId' TEXT,
'flightNr' INTEGER,
'1' REAL,
...
'71' REAL
);
CREATE INDEX sec on data(year,month,day,sec);
What a nasty schema! What exactly do the '1'...'71' fields represent? Are
they all used in each row? If not, you might be better off putting the
data in a seperate table and joining the data.
I experience a big variability of time a query needs:
~database> time sqlite3 data.db 'select "14" from data where campId="polstar98" and
"14">-9999;' >/dev/null
real 0m3.115s
user 0m1.748s
sys 0m1.368s
~/database> time sqlite3 data.db 'select "14" from data where campId="polstar98" and
"14">-9999;' >/dev/null
real 0m3.139s
user 0m1.756s
sys 0m1.380s
~/database> time sqlite3 data.db 'select "42" from data where campId="stream94" and
"14">-9999;' >/dev/null
real 0m50.227s
user 0m4.692s
sys 0m3.028s
I've tried it with and without index and at different times. Most of the
time such simple queries take about 35 sec. Why is that so and what can
be done? I can live with 3 sec of response but not up to one minute and
the database is still not complete. Would pytables with hdf5 be an
alternative? Knowing that this is probably not the right place to ask...
The rows are probably quite big (each real value is 8 bytes), and would
not fit in a single low level SQLite BTree cell, but instead overflow
using an overflow page per row. As well as being inefficient for access of
columns in the overflow page, it is also massively space inefficient, as
the overflow page is not shared and most of it's space is probably wasted.
Tips, suggestions, recommendation are gratefuly appreciated!
If you can't change the schema, your best bet is to increase the page size
of the database, which will hopefully allow you to keep entire rows
together without using overflow pages. Create a new database, and use:
PRAGMA page_size=4096;
then import your existing data from your old database. Something like:
$ rm new.db
$ sqilte3 new.db
sqlite> PRAGMA page_size=4096;
sqlite> ATTACH 'old.db' AS old;
sqlite> CREATE TABLE data AS SELECT * FROM old.data;
Thanks in advance
Hubertus
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
