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(
> instant DATETIME,
> instant_msec SMALLINT UNSIGNED,
> events_int0 INT UNSIGNED,
> events_int1 INT UNSIGNED,
> events_int2 INT UNSIGNED,
> events_int3 INT 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 loooong)!!!
>
> 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 http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
for more info on the julian date support.
CREATE TABLE events(
time REAL, -- julian day number
events_int0 INT UNSIGNED,
events_int1 INT UNSIGNED,
events_int2 INT UNSIGNED,
events_int3 INT UNSIGNED
);
You may want to combine your event ints into a blob field as well if you
really need more than 64 event bits (since SQLite uses 64 bit integer
values).
With the table using real time stamps, you can create an index on the
time field to greatly speed up your time based comparisons.
CREATE INDEX event_time on events(time);
Now your queries can be simplified to:
DELETE FROM events
WHERE time < julianday('2008-06-16 10:21:55.806');
and
SELECT * FROM events
WHERE time between julianday('2008-06-16 00:00:00.000')
and julianday('2008-06-16 23:59:59.999');
These queries will use the index on time to quickly select the records
of interest and ignore all the other records.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
