On 7 Oct 2010, at 5:05pm, Michele Pradella wrote: > The DB is indexed by a DateTime column (is a 64 bit integer)
Do make sure that that column is declared as INTEGER and that there is an index on it. > DELETE FROM table_name WHERE DateTime<DateValue > > 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? Do you have many indexes on that table ? Or any complicated ones ? It might be worth doing something like BEGIN DROP all indexes on the table apart from the one on DateTime DELETE the records CREATE all the dropped indexes again COMMIT On the other hand you say you're deleting about 5 million records out of 23, so that might not help. > I already try to put the > DELETE statement between a BEGIN; COMMIT; statement, but same result. Without declaring transactions, one statement (i.e. one DELETE command) is one transaction, no matter how many records it has an effect on. As you've discovered. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
