I said this before but never saw a response... Just put your delete inside a select loop so it will always be interruptable. I assume you are talking about writing your own code in C or such? So rather than DELETE FROM TABLE WHERE datetime < oldtime; Do this; SELECT datetime from TABLE where datetime < oldtime; BEGIN for each record DELETE FROM TABLE where datetime=thistime; COMMIT I don't know how distinct your datetime values are...this delete could delete multiple records at once which would be OK. One other possibility is to break out your delete statements like this: DELETE FROM TABLE WHERE datetime < oldtime AND ((datetime % 100)=0) DELETE FROM TABLE WHERE datetime < oldtime AND ((datetime % 100)=1) DELETE FROM TABLE WHERE datetime < oldtime AND ((datetime % 100)=2) DELETE FROM TABLE WHERE datetime < oldtime AND ((datetime % 100)=3) ... So each delete would be approx 100 times faster and would allow interruption inbetween deletes. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Michele Pradella Sent: Mon 10/11/2010 4:56 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Speed up DELETE of a lot of records I know that in this use case UPDATE is lither than DELETE, but if I make a DELETE at 4am I can have the DB locked for a lot of time at 4am: I'm only shift the problem. It's not a problem of interface efficiency for user experience: the goal is to make the system always reactive without slow down all the other DB operation. Il 11/10/2010 11.46, Simon Slavin ha scritto: > On 11 Oct 2010, at 10:26am, Michele Pradella wrote: > >> Soft delete could increase the SELECT speed because you have to check >> always for the "deleted" column. >> Moreover the DB will grow up without limit if no one physically delete >> the records: anyway UPDATE of a lot of records could be expensive too. >> I think the only way is to DELETE more frequently less records. > I recommend that you try it. At least in the command-line application if not > in your own code. Deleting a record is very expensive in terms of time and > resources. Changing a field from a 1 to a 0 is far cheaper and faster, even > if it's in an index or two. > > You can have a regular task that runs at 4am that deletes all the records > with a 0 in. That bit is easy. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > 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 *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> *http://www.selea.com* <http://www.selea.com*/> _______________________________________________ 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
