Re: [sqlite] Delete performance vs. Insert performance
Ah! I found my fault in the test code. The delete performance was measured incorrectly, so I re-examed the performance. It still shows outstanding performance compared to insert performance, but it is a reasonable gap. [Cache: 1MB ] Delete 1000recs/3000recs : 2.6 secs . Insert 2000recs : 17secs . [Cache: 100KB] Delete 1000recs/3000recs : 10.8 secs . Insert 2000recs : 28secs . On 6/22/06, Insun Kang [EMAIL PROTECTED] wrote: Hi guys. Thank all of you for reply. I think I need to inform more details about my test-bed to you. The machine is iPAQ h5550 (CPU speed is about 400MHz). Cache size = 500 pages * 2KB = 1MB Cache size = 50 pages * 2KB = 100KB Cache size = 25 pages * 2KB = 50KB The test code is written in c code and the flow is like this. - The data table has 11 columns and 5 single-column indices and 5 multi-column indices. - insert 3000 recs within a single transaction. (begin / insert 3000 recs / commit) INSERT INTO MDS VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11) * I use sqlite3_prepare() and sqlite3_bind_xxx() functions. The data bind time can be ignored because all data to be binded are already loaded in memory before the transaction begins. - delete 1000 recs among 3000 recs within a single transaction (begin / delete 1000 recs / commit ) DELETE FROM T WHERE eleven LIKE :1 * I do not think sqlite3 uses drop rebuild scheme for this SQL statement. One possible scheme that I guess is sqlite3 removes recs only from data table not from all indices. (lazy update for indices) But I am not certain. Any ideas? Thank you in advance. --- create table index create table T ( one text NOT NULL, two text, tree int, four text, five int, six int NOT NULL, seven int, eight int, nineint, ten int UNIQUE, eleven text ) create index i1 ~ i5 (single column indices) create index mi1 ~ mi5 (multi column indices : consists of 2 or 3 columns) On 6/22/06, Dennis Cote [EMAIL PROTECTED] wrote: Insun Kang wrote: Hi. I tested big deletes performance and big insert performance on a Windows CE device in various cache size configurations. ( 1MB, 100KB, 50KB ) Insert 3000 records performs within 23sec, 43sec and 61sec, with respect to each cache size configuration. However, delete 1000 records among 3000 records performs within about 0.4secs for all cache size configurations. Why does delete operation outperform insert operation? and how come the delete operation is independent of cache size? I think the updating indices costs are almost same in both insert and delete operations. Can anybody explain the reason? Insun, I think that you must be doing your inserts as 3000 separate transactions. Try wrapping the inserts with a pair of begin transaction/commit transaction commands. I just wrote a test script to try doing something similar to what you described using the sqlite3 shell. I get times of 8 seconds for 64K + 128K inserts with one preexisting index, and 4 seconds to delete the first 64K records from that table. The script is: create table t1(a integer, b text); create table t2(a integer, b text); create table t3(a integer, b text); create table times( id integer primary key, t timestamp default current_time ); -- create 64K records in t1 begin; insert into t1 values( random(), 'test'); insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; -- create 128K records in t2 insert into t2 select random(), b from t1; insert into t2 select random(), b from t1; commit; -- index the random numbers create index t3_a on t3(a); -- insert all records into t3 with an index in one transaction insert into times(id) values(1); insert into t3 select * from t1 union select * from t2; insert into times(id) values(2); -- delete one third of records from t1 insert into times(id) values(3); delete from t3 where a in (select a from t1); insert into times(id) values(4); -- display times select 'inserts: ' || round(86400 * (julianday((select t from
[sqlite] Delete performance vs. Insert performance
Hi. I tested big deletes performance and big insert performance on a Windows CE device in various cache size configurations. ( 1MB, 100KB, 50KB ) Insert 3000 records performs within 23sec, 43sec and 61sec, with respect to each cache size configuration. However, delete 1000 records among 3000 records performs within about 0.4secs for all cache size configurations. Why does delete operation outperform insert operation? and how come the delete operation is independent of cache size? I think the updating indices costs are almost same in both insert and delete operations. Can anybody explain the reason? -- Insun Kang
Re: [sqlite] Delete performance vs. Insert performance
Hi guys. Thank all of you for reply. I think I need to inform more details about my test-bed to you. The machine is iPAQ h5550 (CPU speed is about 400MHz). Cache size = 500 pages * 2KB = 1MB Cache size = 50 pages * 2KB = 100KB Cache size = 25 pages * 2KB = 50KB The test code is written in c code and the flow is like this. - The data table has 11 columns and 5 single-column indices and 5 multi-column indices. - insert 3000 recs within a single transaction. (begin / insert 3000 recs / commit) INSERT INTO MDS VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11) * I use sqlite3_prepare() and sqlite3_bind_xxx() functions. The data bind time can be ignored because all data to be binded are already loaded in memory before the transaction begins. - delete 1000 recs among 3000 recs within a single transaction (begin / delete 1000 recs / commit ) DELETE FROM T WHERE eleven LIKE :1 * I do not think sqlite3 uses drop rebuild scheme for this SQL statement. One possible scheme that I guess is sqlite3 removes recs only from data table not from all indices. (lazy update for indices) But I am not certain. Any ideas? Thank you in advance. --- create table index create table T ( one text NOT NULL, two text, tree int, four text, five int, six int NOT NULL, seven int, eight int, nineint, ten int UNIQUE, eleven text ) create index i1 ~ i5 (single column indices) create index mi1 ~ mi5 (multi column indices : consists of 2 or 3 columns) On 6/22/06, Dennis Cote [EMAIL PROTECTED] wrote: Insun Kang wrote: Hi. I tested big deletes performance and big insert performance on a Windows CE device in various cache size configurations. ( 1MB, 100KB, 50KB ) Insert 3000 records performs within 23sec, 43sec and 61sec, with respect to each cache size configuration. However, delete 1000 records among 3000 records performs within about 0.4secs for all cache size configurations. Why does delete operation outperform insert operation? and how come the delete operation is independent of cache size? I think the updating indices costs are almost same in both insert and delete operations. Can anybody explain the reason? Insun, I think that you must be doing your inserts as 3000 separate transactions. Try wrapping the inserts with a pair of begin transaction/commit transaction commands. I just wrote a test script to try doing something similar to what you described using the sqlite3 shell. I get times of 8 seconds for 64K + 128K inserts with one preexisting index, and 4 seconds to delete the first 64K records from that table. The script is: create table t1(a integer, b text); create table t2(a integer, b text); create table t3(a integer, b text); create table times( id integer primary key, t timestamp default current_time ); -- create 64K records in t1 begin; insert into t1 values( random(), 'test'); insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; insert into t1 select random(), b from t1; -- create 128K records in t2 insert into t2 select random(), b from t1; insert into t2 select random(), b from t1; commit; -- index the random numbers create index t3_a on t3(a); -- insert all records into t3 with an index in one transaction insert into times(id) values(1); insert into t3 select * from t1 union select * from t2; insert into times(id) values(2); -- delete one third of records from t1 insert into times(id) values(3); delete from t3 where a in (select a from t1); insert into times(id) values(4); -- display times select 'inserts: ' || round(86400 * (julianday((select t from times where id = 2)) - julianday((select t from times where id = 1)) ), 0) || ' seconds'; select 'deletes: ' || round(86400 * (julianday((select t from times where id = 4)) - julianday((select t from times where id = 3)) ), 0) || ' seconds'; This script produces the following results. C:\Documents and Settings\DennisCsqlite3 test.db3 test.sql inserts: 8.0 seconds deletes: 4.0 seconds Note that my inserts into t3 all happen
