I think that if the total records are 23 millions and the record to delete is 5 millions, creating a temp table of 23-5=18millions records is slower than deleting directly 5millions records. Anyway, the table has got only 1 index on the DateTime column that is INTEGER. I dropping all index deleting and recreate index is the slowest operation I can do on the DB, so I can not use it. I can try to Delete on Primary Key instead on the DateTime, but I think that the slowest operation is the deletion of the record not the selection. Further more I can not be sure that the Primary Key is always incremental, usually should be, but if for example the Primary key wrap could not be the right thing to do. I don't know if could be faster to do more Delete of less records, or perhaps making a VIEW and than deleting all the records matching the VIEW. I'm thinking about this to find the fastest solution, because the problem is that when sqlite is deleting the records obviously I can not access the DB for insert new records and all the operations have to wait for delete complete...and 4-5minutes is too much time to wait.
Il 07/10/2010 19.14, P Kishor ha scritto: > On Thu, Oct 7, 2010 at 11:05 AM, Michele Pradella > <[email protected]> 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 >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > -- Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *[email protected]* <mailto:[email protected]> *http://www.selea.com* _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
