First, thanks for all your answers.
I would like to come back to my question (see first post).
All events come sequentially (because it's a time line).
I can have more than one event during the same time but I use 1 different
bit to identify each event types.
I think it's important to use this sequential constraint to do the delete
request.
But SQLite3 use a B-Tree algorithm...
When I delete some rows it's always for oldest events (before a specific
time = 'Ts').
I mean I delete all rows before 'Ts' (like a fifo...)
I don't want to check all my event table but only the oldest events (first
rows).
When I find a time greater than 'Ts' I can stop the delete operation.
Do you think It's better to use one more table for optimization?
For example, creating a "summarised" table which could, in 1 row, summarise
all the events (detailed in the general table) that have happened in a time
lapse (= time between
two rows). This "summarised" table could be created with the following
request :
CREATE TABLE bloc_events(
bloc_idINTEGER NOT NULL PRIMARY KEY,
bloc_start_timeREAL,
events_resume BLOB
);
And now my event table looks like this:
CREATE TABLE events(
time REAL NOT NULL PRIMARY KEY,
event BLOB,
bloc_idINTEGER
);
'events_resume' is a logical 'OR' result of all event types detected in the
correspondent event groups in the events table.
'bloc_start_time' is the start time of a new group of events.
The difference between two successive bloc_events rows is the group
precision (maybe 10 minutes)
To know the oldest event (bloc_id) to delete I check bloc_events table
first.
Then I delete row on events table with the same bloc_id found on bloc_events
table.
How to create a FOREIGN KEY with SQLite3?
Thanks in advance for your answers.
Regards,
Raphaël
-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] la part de Dennis Cote
Envoyé : mardi 17 juin 2008 18:59
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Long delay for delete/select row in database
Raphaël KINDT wrote:
>
> I'm working on a video monitoring program that uses frame grabber (12
> cameras).
> This program records videos and detects some (input/output) events such as
:
> motion detection, blue screens (cut camera cable), I/Os 1 to 24, and much
> more...
>
> I save all events in a database (sqlite3) with an INSERT request directly
> after their detection.
> Here is the table I use:
>
> CREATE TABLE events(
>instantDATETIME,
>instant_msec SMALLINT UNSIGNED,
>events_int0INT UNSIGNED,
>events_int1INT UNSIGNED,
>events_int2INT UNSIGNED,
>events_int3INT UNSIGNED
> );
>
> As you can see I don't use PRIMARY KEY and INDEX...
> Each bits of events_intx is a particular event detected (1 = detected).
>
> When a video is too old, I delete it.
> But I must delete all events detected during this video record.
> To do that I use a SQL request described hereunder:
>
> DELETE FROM events WHERE ( (instant < datetime('2008-06-16 10:21:55.806'))
> OR ((instant = datetime('2008-06-16 10:21:55.806')) AND (instant_msec <=
> 806)) );
>
> Sometimes a 'client' wants some information of "what's happened yesterday"
> and he sends a request such as:
>
> SELECT * FROM events WHERE ( (instant < datetime('2008-06-16
23:59:59.999'))
> OR ((instant = datetime('2008-06-16 23:59:59.999')) AND (instant_msec <=
> 999)) ) AND ( (instant > datetime('2008-06-16 00:00:00.000')) OR ((instant
=
> datetime('2008-06-16 00:00:00.000')) AND (instant_msec >= 0)) ) ORDER BY
> instant, instant_msec;
>
> All this works well for a 'light' database.
> But I have forced the application to detect a lot of motion detection
events
> (each second during 10 days).
> Thus, each second, my application is using INSERT to add a row to the
event
> table.
> Now my database's size is about 120 MB... Very big...
> When the DELETE (or SELECT) request starts, it takes about 75 seconds to
be
> executed (very, very lng)!!!
>
> How can I reduce this time for a big database?
> I must have a delay that doesn't go up to 40 msec to be efficient.
> Do I have to split my database into several small databases to reduce this
> delay?
> Do I have to create another table and use PRIMARY KEY, INDEX etc?
>
I would recommend that you change your database schema an combine your
timestamp string and subsecond integer field into a single floating
point julian day number that provides both pieces of information in
units of days (since the julian day epoch). The integer portion is the
number of days, the fractional part is the point in the day to very high
resolution (because SQLite uses 64 bit double precision floating point
values). See
