DELETE on PrimaryKey instead of DateTime index takes same time
Il 08/10/2010 10.30, Michele Pradella ha scritto:
> 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.
> 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
>>>
>>>
>
--
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
