On Thu, Oct 7, 2010 at 11:05 AM, Michele Pradella <michele.prade...@selea.com> wrote: > Hi all, I have a question about how to speed up a DELETE statement. > I have a DB of about 3GB: the DB has about 23 millions of records. > The DB is indexed by a DateTime column (is a 64 bit integer), and > suppose you want to delete all records before a date. > Now I'm using a syntax like this (I try all the statement with the > sqlite shell): > suppose to use __int64 DateValue=the date limit you want to delete > > DELETE FROM table_name WHERE DateTime<DateValue >
What is the speed of SELECT FROM table WHERE DateTime >= DateValue; If the above speed is acceptable, then try the following CREATE TABLE tmp AS SELECT FROM table WHERE DateTime >= DateValue; DROP TABLE table; ALTER TABLE tmp RENAME to table; > the statement is trying to delete about 5 millions records and it takes > about 4-5minutes. > Is there a way to try to speed up the DELETE? I already try to put the > DELETE statement between a BEGIN; COMMIT; statement, but same result. > After the delete complete I have a -wal file of about 600MB: this file > is not deleted even if I disconnect from the database. > Is that the right behavior? I thought that when the last DB connection > terminate the -wal file is reintegrated in the DB, but it's not. > > _______________________________________________ > 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
