Re: [sqlite] Long delay for delete/select row in database
Raphaël KINDT wrote: > > 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? > A normal index on time will allow SQLite to locate the rows with time less than Ts very quickly. I don't see any need for another table to optimize this function. > 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? > SQLite allows you to use foreign keys for joins, but it does not enforce any referential integrity when rows are deleted. There are triggers you can add to your table to do this if need be, but it is often just a easy to do it in your application. See http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers for more info. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
Alexey Pechnikov wrote: > > P.S. Is any method for multy-master replication of SQLite databases? Nothing built in, but I believe several user have developed their own systems to do this. You would probably be better off asking this question in a new thread. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
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
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Thursday 19 June 2008 02:37:57 Dennis Cote написал(а): > I'm not sure if you even need unique id numbers for these records, or > why you are concerned that there might be multiple records with the same > save_date for that matter? Is this table linked to any others? I'm using URL /../[save_date] or /../[ROWID] for my site. Well, I can use second key for host identity as /../[host_id]/[ROWID] and bind user session to the host. P.S. Is any method for multy-master replication of SQLite databases? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
Alexey Pechnikov wrote:
>
> I have same database on host A and host B. On _both_ hosts I can
> insert/update/delete/select rows and periodically synchronize databases.
>
Oh. That's a different issue that you didn't mention before. It
definitely makes things more complicated.
> CREATE TABLE photos
> (
> name TEXT NOT NULL,
> filename TEXT NOT NULL,
> hostname TEXT NOT NULL DEFAULT 'A',
> save_date REAL COLLATE BINARY,
> update_date REAL COLLATE BINARY,
> delete_date REAL COLLATE BINARY
> );
>
> CREATE TRIGGER photos_delete before delete on photos begin
> update photos set delete_date = julianday('now') where rowid=old.rowid and
> delete_date IS NULL;
> select RAISE (IGNORE);
> end;
> CREATE TRIGGER photos_insert after insert on photos begin
> update photos set save_date = coalesce(save_date, julianday('now')) where
> rowid=new.rowid;
> end;
> CREATE TRIGGER photos_update after update on photos begin
> update photos set update_date = julianday('now') where rowid=old.rowid;
> end;
>
> For replication I can select all rows updated by selected period and send it
> to other hosts.
>
> ROWID can duplicate on host A and host B for different rows. But the chances
> of timestamp unique are good. So, I select item by save_date form host A or
> host B equally.
>
Yes, the internal rowid's from one database are meaningless in another
(much like pointers on one machine being meaningless on another). You
can use an explicit integer primary key id column, but then you would
need to use different id ranges on the different hosts in order to avoid
collisions when the records are copied.
It seems to me that you are still quite likely to get multiple records
with the same timestamp though. If you have lots of activity, eventually
both hosts will have and event occur at the same time.
One thing you may want to try is using the 64 bit random numbers
produced by random() as ids. The chances of any two out of even millions
of random 64 bit numbers colliding is very very small. Just ensure your
random number generators are seeded with different values (perhaps use
the host's IP address) so they generate different sequences.
I'm not sure if you even need unique id numbers for these records, or
why you are concerned that there might be multiple records with the same
save_date for that matter? Is this table linked to any others?
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Thursday 19 June 2008 00:22:37 Dennis Cote написал(а):
> Alexey Pechnikov wrote:
> > I'm replicating my database using sqlite dump and load or sql queries.
> > I'm not sure that rowid is not different after that.
>
> If you assign each row an id (and even better make it an integer primary
> key) and use those id columns to link related records then all your
> links will survive across a dump and reload sequence.
>
> However, that shouldn't matter for the a single session with a loaded
> database. Your "other question" is returning timestamp based on some
> criteria, it could simply return a rowid instead of a timestamp. The
> rowid is valid for the entire session with the database.
>
> > Can I do it from tcl? And how use index?
>
> I'm not sure how you handle floating point values in TCL since it is
> typeless. It may not be possible to avoid the conversions. This is even
> more reason to use ranges to locate records by time, and rowids to
> locate specific records.
I have same database on host A and host B. On _both_ hosts I can
insert/update/delete/select rows and periodically synchronize databases.
CREATE TABLE photos
(
name TEXT NOT NULL,
filename TEXT NOT NULL,
hostname TEXT NOT NULL DEFAULT 'A',
save_date REAL COLLATE BINARY,
update_date REAL COLLATE BINARY,
delete_date REAL COLLATE BINARY
);
CREATE TRIGGER photos_delete before delete on photos begin
update photos set delete_date = julianday('now') where rowid=old.rowid and
delete_date IS NULL;
select RAISE (IGNORE);
end;
CREATE TRIGGER photos_insert after insert on photos begin
update photos set save_date = coalesce(save_date, julianday('now')) where
rowid=new.rowid;
end;
CREATE TRIGGER photos_update after update on photos begin
update photos set update_date = julianday('now') where rowid=old.rowid;
end;
For replication I can select all rows updated by selected period and send it
to other hosts.
ROWID can duplicate on host A and host B for different rows. But the chances
of timestamp unique are good. So, I select item by save_date form host A or
host B equally.
P.S. Yes, multy-master replication is very complex but very helpfull... I try
use simple way. Are exists any better choices?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
Alexey Pechnikov wrote: > > I'm replicating my database using sqlite dump and load or sql queries. I'm > not > sure that rowid is not different after that. > If you assign each row an id (and even better make it an integer primary key) and use those id columns to link related records then all your links will survive across a dump and reload sequence. However, that shouldn't matter for the a single session with a loaded database. Your "other question" is returning timestamp based on some criteria, it could simply return a rowid instead of a timestamp. The rowid is valid for the entire session with the database. >> >>double time = sqlite3_column_double(s1, 1); >> >>sqlite3_bind_double(s2, 1, time); >> >> If you do this, it should be possible to compare these values for >> floating point equality. > > Can I do it from tcl? And how use index? I'm not sure how you handle floating point values in TCL since it is typeless. It may not be possible to avoid the conversions. This is even more reason to use ranges to locate records by time, and rowids to locate specific records. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Wednesday 18 June 2008 22:44:16 Dennis Cote написал(а): > Alexey Pechnikov wrote: > > I find get only _one_ row. I found correspond timestamp by other > > questions. I don't want get more than one row. > > In that case you would be better off to get the rowid of that row using > the other questions. Then you can get the row of interest directly using > the rowid. > I'm replicating my database using sqlite dump and load or sql queries. I'm not sure that rowid is not different after that. > You should not be linking rows using timestamps, but if you must, it is > important to avoid calculations with and conversions to and from > floating point. If you retrieve a floating point value from one query > and pass it back as a parameter to another query, you should ensure that > it remains as double all along the way. > >double time = sqlite3_column_double(s1, 1); > >sqlite3_bind_double(s2, 1, time); > > If you do this, it should be possible to compare these values for > floating point equality. Can I do it from tcl? And how use index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
Alexey Pechnikov wrote: > > I find get only _one_ row. I found correspond timestamp by other questions. I > don't want get more than one row. > In that case you would be better off to get the rowid of that row using the other questions. Then you can get the row of interest directly using the rowid. You could also use a limit clause to get the first result within a period if it is possible that your query could return more than one. This is really an important question, does your data allow more than one event within the same millisecond period? If so, how do you distinguish them? You should not be linking rows using timestamps, but if you must, it is important to avoid calculations with and conversions to and from floating point. If you retrieve a floating point value from one query and pass it back as a parameter to another query, you should ensure that it remains as double all along the way. double time = sqlite3_column_double(s1, 1); sqlite3_bind_double(s2, 1, time); If you do this, it should be possible to compare these values for floating point equality. Again, I have found it is best to think of events as happening within a (possibly quite small) window of time, rather than at a point in time. The difference is that identifying a point in time requires infinite precision, whereas identifying a period requires finite user defined precision. The window you need may be small but even a millisecond has a beginning and an end. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Wednesday 18 June 2008 21:32:46 Dennis Cote написал(а):
> Alexey Pechnikov wrote:
> > There is problem for select one row by time - we must use query
> > select * from events where time || =julianday('2008-06-16 23:59:59.999');
> > and index is not used for this query.
> > Maybe patch http://www.sqlite.org/cvstrac/chngview?cn=5215
> > resolved problem but I'm not sure.
>
> There is no problem with this except that it is usually unreliable to do
> equality comparisons with floating point data such as julian day numbers.
>
> When you say "select one row by time" do you mean that there can only be
> one row in each millisecond period, or do you really mean that you want
> all the rows within that one millisecond period, or perhaps you only
> want the first row in that millisecond period.
>
> In any case, the secret is to know what your required time resolution
> is, and use a normal range query to select rows using the start and end
> times of that period. For a one millisecond long period you can use:
>
>select * from events
>where time between julianday('2008-06-16 23:59:59.999')
> and julianday('2008-06-16 23:59:59.999') + 1.0/(8640)
>
> or
>
>select * from events
>where time between julianday('2008-06-16 23:59:59.999')
> and julianday('2008-06-17 00:00:00.000')
>
I find get only _one_ row. I found correspond timestamp by other questions. I
don't want get more than one row.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Tuesday 17 June 2008 20:59:19 Dennis Cote написал(а):
> 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).
There is problem for select one row by time - we must use query
select * from events where time || =julianday('2008-06-16 23:59:59.999');
and index is not used for this query.
Maybe patch http://www.sqlite.org/cvstrac/chngview?cn=5215
resolved problem but I'm not sure.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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_int0INT UNSIGNED,
events_int1INT UNSIGNED,
events_int2INT UNSIGNED,
events_int3INT 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
Hi Raphaël,
Since you are dealing with intervals of numbers, perhaps recent
discussion of
rtree indexing will help.
Besides, separating instant into highly used level of unit may be also
useful.
For example, instant_date and instant_time.
Another rough method is applying PRAGMA synchronous = OFF and/or
PRAGMA journal_mode = OFF.
Regards,
/Mike/
Raphaël KINDT wrote:
> 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(
>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?
>
> 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
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Long delay for delete/select row in database
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(
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?
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
