Michele Pradella wrote: > As I explain in previews email, I think that recreating the index is > the slowest operation I can do on my DB. > Anyway in my first email I ask another question about -wal file > Tryin to DELETE the (5 millions) records with the shell SQLITE interface > I can see the -wal grow up till 600MB. I can not understand why the -wal > is no more reintegrated, and even when I close the connection (closing > the shell) -wal file still exist. > Trying for example the statement "create table new as select * from > current where condition = keep;" the -wal file grow up till 1,5GB and > than the same, after closing shell the -wal remain. > > Moreover the operation above "create new...." terminate with a "Error: > disk I/O error" > The hard disk I use has a lot of free space and it's SATA2 hard disk, so > is internal
You may try with the new 3.7.3 version, the 3.7.2 doesn't operate very well on win32 when doing huge transactions in wal mode. 2nd, when running sqlite with a gigabyte sized database it is useful to heavily increase the cache size, not sure if that helps for delete statements though, but it does in particular when creating indices. Marcus > > Il 07/10/2010 20.38, Petite Abeille ha scritto: >> On Oct 7, 2010, at 6:05 PM, Michele Pradella wrote: >> >>> I have a DB of about 3GB: the DB has about 23 millions of records. [..] >>> 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? >> Considering that you want to delete about a quarter of the records, perhaps >> it would be more efficient to recreate that table altogether, no? >> >> Pseudocode: >> >> create table new as select * from current where condition = keep; >> create index on new; >> drop table current; >> alter table rename new to current; >> >> _______________________________________________ >> 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