Michele Pradella wrote: > ok I'll try with 3.7.3 > DELETE is a little bit faster, and the -wal is reintegrated when I close > the connection. > Changing cache_size (I try 10000) DELETE takes same time to complete.
10000 doesn't sounds very big, I used to define it to e.g. 320000 instead when working with a GB size DB (mainly for index creation and "PRAGMA integrity_check;") Maybe playing with temporary setting PRAGMA synchronous = OFF; would be useful, although I think the wal mode should already avoid too much syncing. Marcus > Was my fault, because to close the shell connection I used Ctrl+C but > this leave the -wal file. If I close with .quit the -wal file is > reintegrated. > I thought that Ctrl+C is like a ".quit " but it's not. > Anyway if I close the DB connection with Ctrl+C and than reopen > connection and close it with .quit the -wal file is not reintegrated. > > Il 08/10/2010 9.56, Michele Pradella ha scritto: >> I'll try to increase cache size, and I'll try operation on my Db with >> the 3.7.3 anyway I already ported the Fix of the WAL issue from recent >> snapshot. I'll try and let you know >> >> Il 08/10/2010 9.52, Marcus Grimm ha scritto: >>> 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 >>> >>> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
