> Don't know if the index is updated after each row delete or after the whole > delete transaction is commited. For the first you can try: > > time sqlite3 trip.db "PRAGMA automatic_index= FALSE; delete from trip where > key<=1400;PRAGMA automatic_index= TRUE; reindex trip"
PRAGMA automatic_index has nothing to do with updating index after deleting each row. And it won't have any impact on this delete statement because it can't use automatic index. Pavel On Thu, Feb 7, 2013 at 2:13 AM, Eduardo Morras <emorr...@yahoo.es> wrote: > On Tue, 5 Feb 2013 12:54:13 +0000 > Jason Gauthier <jgauth...@lastar.com> wrote: > >> Hey Everyone, >> >> I am a fairly new user of sqlite, but not particularly new to SQL >> principles. I am developing an application that will run on a low end >> system. >> Not quite embedded, but not quite a PC. In my application, I do frequent >> table deletes. My results have been poor, and I am looking for someone to >> tell me "I'm > doing it wrong", or maybe "that's the best you're going to >> get", etc. > >> Any thoughts on why this may be so slow, or what I can do to improve it? > > Don't know if the index is updated after each row delete or after the whole > delete transaction is commited. For the first you can try: > > time sqlite3 trip.db "PRAGMA automatic_index= FALSE; delete from trip where > key<=1400;PRAGMA automatic_index= TRUE; reindex trip" > > If you delete a high percentage of the table rows, it's faster select the > data to save in a new table, drop original table and "alter table temp rename > to trip". If you use a memory temp table and a powerloss happens, your data > is lost, use a non-temp table. > > The trick of adding a new column for mark dirty rows will not work because > you are using a sd-card, the cost of mark as delete/dirty those rows is > greater than deleting them. > > >> >> Thanks, >> >> Jason >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > --- --- > Eduardo Morras <emorr...@yahoo.es> > _______________________________________________ > 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
