Hi everybody,
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?
Perhaps I can create a database only in memory and another database in a
file.
The database in memory is used to do the seeking and the database in file is
used for the backup.
Those databases must be synchronized...
Thanks for all advices.
KINDT Raphaël
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
