[Default] On Tue, 20 Nov 2007 09:56:11 +1100, James Steward
<[EMAIL PROTECTED]> wrote:
>Hi,
>
>I am new to SQL and SQLite, so please excuse me if I appear thick at
>times.
>
>I have an application that generates data from a moving vehicle. At a
>position there may be 10s of pairs of data. Position is given as an
>integer, and the data pairs are an integer and a float (real). So I set
>up a database like this (in Tcl);
>
>db eval {CREATE TABLE position(\
> position_id INTEGER PRIMARY_KEY, \
> odo INTEGER, \
> time CURRENT_TIMESTAMP);}
>db eval {CREATE TABLE data(\
> data_id INTEGER PRIMARY_KEY, \
> position_id INTEGER, \
> x INTEGER, \
> y REAL);}
Shouldn't that be PRIMARY KEY (without the underscore)?
>So far so good. Now I have a heap of stored data in a binary file, that
>I use to insert into the tables, with statements like;
>
>db eval {BEGIN;}
>
># loop reading in file...
>while {..} {
>
>#when a new odo value is read...
>db eval {INSERT INTO position(odo) values($odo);}
>set pos_id [db eval {SELECT last_insert_rowid();}]
>
>#for every data pair
>db eval {INSERT INTO data(position_id, x, y) values($pos_id, $x, $y);}
>
>}
>
>db eval {COMMIT;}
>
>There are no errors returned. Now I try a couple of queries, which
>return data, but not quite what I expect.
>
>#This kinda works as expected, but position_id and time columns are not
>#poulated. Why not?
That will have to do with the wrong spelling of PRIMARY KEY.
>db eval {SELECT * FROM position WHERE ROWID BETWEEN 100 AND 101;}
>
>#This does not work, returns nothing. I thought (after reading the
>#SQLite doco) that position_id would be populated with ROWID...
>db eval {SELECT * FROM position WHERE position_id BETWEEN 100 AND 101;}
>
>#This kinda works, but data_id is not populated...
>db eval {SELECT * FROM data where position_id BETWEEN 100 AND 101;}
>
>#A more complicated query...runs quite slowly. How can this be sped up?
>db eval {SELECT position.odo, data.x, data.y from position, data WHERE
>position.odo BETWEEN 10000 AND 10020;}
An index on position.odo might help.
Try that and compare the 'EXPLAIN SELECT ...' and 'EXPLAIN QUERY
PLAN SELECT ...' output with and without index.
>Is SQLite going to be able to handle, say, 2,000,000 data pairs, and say
>60,000 positions, efficiently and quickly?
In general, yes.
>How can I help SQLite perform queries like the last above, as fast as
>possible?
Indexes and pragmas. More specifically, the page_size and
cache_size pragmas.
>Regards,
>James.
HTH
--
( Kees Nuyt
)
c[_]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
