Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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');
- SQLite: 4.673
- SQLite Secure: 6.303
- Slowdown: 34.882
- 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;
- SQLite: 0.594
- SQLite Secure: 0.697
- Slowdown: 17.425
- Test 3: 100 SELECTs without an index
- SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
- SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
- SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;
- ... 94 lines omitted
- SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;
- SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
- SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
- SQLite: 1.037
- SQLite Secure: 1.023
- Slowdown: -1.305
- Test 4: 100 SELECTs on a string comparison
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';
- ... 94 lines omitted
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
- SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
- SQLite: 3.207
- SQLite Secure: 3.229
- Slowdown: 0.706
- Test 5: Creating an index
- CREATE INDEX i2a ON t2(a);
- CREATE INDEX i2b ON t2(b);
- SQLite: 0.264
- SQLite Secure: 0.424
- Slowdown: 60.558
- 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;
- SQLite: 1.010
- SQLite Secure: 1.094
- Slowdown: 8.237
- 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;
- SQLite: 0.344
- SQLite Secure: 0.454
- Slowdown: 31.951
- 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;
- SQLite: 1.230
- SQLite Secure: 1.422
- Slowdown: 15.605
- 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;
- SQLite: 0.980
- SQLite Secure: 1.303
- Slowdown: 33.032
- Test 10: INSERTs from a SELECT
- BEGIN;
- INSERT INTO t1 SELECT * FROM t2;
- INSERT INTO t2 SELECT * FROM t1;
- COMMIT;
- SQLite: 2.935
- SQLite Secure: 1.794
- Slowdown: -38.880
- Test 11: DELETE without an index
- DELETE FROM t2 WHERE c LIKE '%fifty%';
- SQLite: 2.081
- SQLite Secure: 1.745
- Slowdown: -16.174
- Test 12: DELETE with an index
- DELETE FROM t2 WHERE a>10 AND a<20000;
- SQLite: 0.733
- SQLite Secure: 0.896
- Slowdown: 22.144
- Test 13: A big INSERT after a big DELETE
- INSERT INTO t2 SELECT * FROM t1;
- SQLite: 1.570
- SQLite Secure: 1.560
- Slowdown: -0.603
- 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;
- SQLite: 0.095
- SQLite Secure: 0.227
- Slowdown: 139.423
- Test 15: DROP TABLE
- DROP TABLE t1;
- DROP TABLE t2;
- SQLite: 0.066
- SQLite Secure: 0.211
- Slowdown: 218.260
Add Comment
Please, Sign In to add comment