I try 320000 and it takes same time. Moreover the RAM used is about 500MB Even with "PRAGMA synchronous = OFF;" operation time is the same. I think the only way for now is to make more DELETE with less element, but it's not the best way
Il 08/10/2010 10.41, Marcus Grimm ha scritto: > 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 > > -- 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
