sqlite 效率

Test 1: 1000 INSERTs CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100)); INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three'); INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty'); ... 995 lines omitted INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine'); INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two'); INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two'); PostgreSQL: 18.255 SQLite 2.7.6: 45.872 SQLite 2.7.6 (nosync): 0.230 Test 2: 25000 INSERTs in a transaction BEGIN; CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100)); INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one'); ... 24997 lines omitted INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven'); INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty'); COMMIT; PostgreSQL: 4.216 SQLite 2.7.6: 1.383 SQLite 2.7.6 (nosync): 1.238 Test 3: 100 SELECTs without an index BEGIN; SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000; SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100; ... 96 lines omitted SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800; SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900; COMMIT; PostgreSQL: 4.225 SQLite 2.7.6: 5.094 SQLite 2.7.6 (nosync): 5.095 Test 4: 100 SELECTs on a string comparison BEGIN; SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%'; SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%'; ... 96 lines omitted SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%'; SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%'; COMMIT; PostgreSQL: 6.210 SQLite 2.7.6: 5.670 SQLite 2.7.6 (nosync): 5.671 Test 5: Creating an index CREATE INDEX i2a ON t2(a); CREATE INDEX i2b ON t2(b); PostgreSQL: 0.351 SQLite 2.7.6: 1.013 SQLite 2.7.6 (nosync): 0.842 Test 6: 5000 SELECTs with an index SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100; SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200; SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300; ... 4994 lines omitted SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800; SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900; SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000; PostgreSQL: 3.367 SQLite 2.7.6: 1.079 SQLite 2.7.6 (nosync): 1.085 Test 7: 1000 UPDATEs without an index BEGIN; UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10; UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20; ... 996 lines omitted UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990; UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000; COMMIT; PostgreSQL: 1.689 SQLite 2.7.6: 0.941 SQLite 2.7.6 (nosync): 0.833 Test 8: 25000 UPDATEs with an index BEGIN; UPDATE t2 SET b=271822 WHERE a=1; UPDATE t2 SET b=28304 WHERE a=2; ... 24996 lines omitted UPDATE t2 SET b=442549 WHERE a=24999; UPDATE t2 SET b=423958 WHERE a=25000; COMMIT; PostgreSQL: 7.881 SQLite 2.7.6: 3.653 SQLite 2.7.6 (nosync): 3.160 Test 9: 25000 text UPDATEs with an index BEGIN; UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1; UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2; ... 24996 lines omitted UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999; UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000; COMMIT; PostgreSQL: 8.586 SQLite 2.7.6: 2.501 SQLite 2.7.6 (nosync): 2.036 Test 10: INSERTs from a SELECT BEGIN; INSERT INTO t1 SELECT * FROM t2; INSERT INTO t2 SELECT * FROM t1; COMMIT; PostgreSQL: 1.942 SQLite 2.7.6: 2.793 SQLite 2.7.6 (nosync): 1.924 Test 11: DELETE without an index DELETE FROM t2 WHERE c LIKE '%fifty%'; PostgreSQL: 0.238 SQLite 2.7.6: 2.896 SQLite 2.7.6 (nosync): 0.638 Test 12: DELETE with an index DELETE FROM t2 WHERE a>10 AND a<20000; PostgreSQL: 0.426 SQLite 2.7.6: 2.085 SQLite 2.7.6 (nosync): 0.977 Test 13: A big INSERT after a big DELETE INSERT INTO t2 SELECT * FROM t1; PostgreSQL: 4.474 SQLite 2.7.6: 2.785 SQLite 2.7.6 (nosync): 1.803 Test 14: A big DELETE followed by many small INSERTs BEGIN; DELETE FROM t1; INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six'); ... 2997 lines omitted INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five'); INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen'); COMMIT; PostgreSQL: 0.849 SQLite 2.7.6: 0.444 SQLite 2.7.6 (nosync): 0.214 Test 15: DROP TABLE DROP TABLE t1; DROP TABLE t2; PostgreSQL: 0.624 SQLite 2.7.6: 0.656 SQLite 2.7.6 (nosync): 0.145