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
