On Thu, Aug 20, 2009 at 8:33 AM, pierr<pierr.c...@gmail.com> wrote:
>
> Hi,
> We have a database with a very simple schema:
> CREATE TABLE IF NOT EXISTS tblIndex(
> frame_type INT,
> pts VARCHAR(5),
> ts_start INT primary key,
> ts_end INT)
>
> And the application scenario is :
> 1. Every second usr will insert 2 ~ 50 records ,and the ts_start fields of
> those records is always increasing. After 8 hours ,there are at most
> 1_800_000 records. By setting the sync mode to off, the performance seems OK
> so far.And because the data of each record has only 16 bytes, we may use
> some buffer even if the insert speed is not too fast.
>
> 2. After 8 hours , usr will told me to delete the oldest data by telling the
> upper bound ts_start , so I will do DELETE FROM tblIndex WHERE ts_start <
> upper_bound_ts_start. Delete 90_000 (which is the records for half a hour)
> out of the 1_800_000 now take 17 seconds. A litter bit longer than expected.
> Any way to reduce this ?
With a simple schema as below (no indexes other than the stock INTEGER
PRIMARY KEY on ts_start
CREATE TABLE tblIndex (
ts_start INTEGER PRIMARY KEY,
frame_type INTEGER,
pts VARCHAR(5),
ts_end INTEGER
)
and using Perl DBD::SQLite on my Macbook, I get the following times
65 seconds to insert 1,800,000 records with a commit every 100,000
1 second to delete the first 90,000 records
> We don't care if the records is synced to the hard
> disk immediately. We are thinking start a seperated thread to do the delete
> so to make this call to be async. The things I am not sure is whether the
> (long time )delete will impact the insert performance if they share the same
> connection? Or should I use a seperated connection for insert and delete?
> But In this way, do they need be synced in application level?
>
> 3. Search. SELECT ts_start FROM tblIndex WHERE ts_start BETWEEN ? AND ? " -
> As ts_start is the primary key so the performance is OK for our need now. I
> am thinking i should use a seperated connection for search , right?
>
> Configuration of sqlite :
> hard disk database (usb interface)
> cache size is 2000
> page size is 1024
> sync mode is 0 (off)
> journal_mode mode is truncate
>
>
> Thanks for any suggestion to improve the delete performance or about the
> overall design.
> --
> View this message in context:
> http://www.nabble.com/suggestion-on-the-database-design-tp25062134p25062134.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
