Re: [sqlite] Sqlite3 delete action is too slow

[PennyH]

Thank you MikeW. I try to use transaction, still slow. Use limit clause is
good idea, but the help is limited. Dose number of index affect speed too?
How many index on one table is better?

Regards,

Penny


PennyH wrote:
> 
> I create a DB and only one table in the DB. There are 3641043 records in
> the DB file. Min id is 27081364, Max id is 30902585.
> 
> I did follow operation:
> 
> sqlite> delete from XXX where userId>3090 and userId<30902000;
> 
> took 1’32’’
> 
>  
> 
> sqlite> delete from XXX where userId>2900 and userId<29902000;
> 
> spent 3 hours and 33minutes and  26secs
> 
>  
> 
> The table schema:
> 
> CREATE TABLE XXX (
> 
>userIdinteger primary key,
> 
>userName  varchar not null,
> 
>c1varchar not null,
> 
>c2 bigint not null,
> 
>c3  varchar,
> 
>c4 varchar not null,
> 
>c5  varchar,
> 
>c6varchar,
> 
>c7 bigint default 0,
> 
>c8bigint default 0,
> 
>c9   integer default 0,
> 
>c10   integer default 0,
> 
>c11 integer default 0,
> 
>c12 bigint default 0,
> 
>c13   bigint default 0,
> 
>c14 integer default 0,
> 
>c15integer default 1,
> 
>c16varchar,
> 
>c17varchar,
> 
>c18 varchar , 
> 
>c19  integer default 0, 
> 
>c20  varchar default '', 
> 
>CONSTRAINT xxx_key UNIQUE (userName, c1, c4, c7)
> 
> );
> 
> CREATE INDEX idx_1 on XXX(c7);
> 
> CREATE INDEX idx_2 on XXX(username,c8);
> 
> CREATE INDEX idx_3 on XXX (c5, c8);
> 
> CREATE INDEX idx_4 on XXX (userName);
> 
> CREATE INDEX idx_5 on XXX (c1);
> 
> CREATE TRIGGER xxx_limit_size after insert on XXX when ((select
> max(userId) from XXX) - (select min(userId) from XXX) > 1000) begin
> delete from XXX where userId < 1000 + (select min(userId) from XXX); end;
> 
>  
> 
> The table is complex. 
> 
>  
> 
> Penny
> 
>  
> 
>  
> 
>  
> 
>  
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Sqlite3-delete-action-is-too-slow-tp20163980p20201132.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 delete action is too slow

[PennyH]

My question is How can improve delete speed? This speed can not satisfy my
application.


Igor Tandetnik wrote:
> 
> "yhuang" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
>> I create a DB and only one table in the DB. There are 3641043 records
>> in the DB file. Min id is 27081364, Max id is 30902585.
>>
>> I did follow operation:
>>
>> sqlite> delete from XXX where userId>3090 and userId<30902000;
>>
>> took 1'32''
>>
>>
>>
>> sqlite> delete from XXX where userId>2900 and userId<29902000;
>>
>> spent 3 hours and 33minutes and  26secs
> 
> If your question is about why the second statment takes so much longer 
> than the first, it's because it deletes 902,000 records vs 2,000 for the 
> first. Did you perhaps mean to write userId>2990?
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Sqlite3-delete-action-is-too-slow-tp20163980p20181076.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users