Ok so the main idea it's always the same: split the DELETE to make the operation on less records, but do it more often.
Il 11/10/2010 13.33, Black, Michael (IS) ha scritto: > 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 -- 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* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
