Guest User

Untitled

a guest
Jul 20th, 2018
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.66 KB | None | 0 0
  1.  
  2. Test 1: 1000 INSERTs
  3.  
  4. CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
  5. INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
  6. INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
  7. ... 995 lines omitted
  8. INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
  9. INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
  10. INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');
  11. SQLite: 4.673
  12. SQLite Secure: 6.303
  13. Slowdown: 34.882
  14.  
  15. Test 2: 25000 INSERTs in a transaction
  16.  
  17. BEGIN;
  18. CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
  19. INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');
  20. ... 24997 lines omitted
  21. INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');
  22. INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');
  23. COMMIT;
  24. SQLite: 0.594
  25. SQLite Secure: 0.697
  26. Slowdown: 17.425
  27.  
  28. Test 3: 100 SELECTs without an index
  29.  
  30. SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
  31. SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
  32. SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;
  33. ... 94 lines omitted
  34. SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;
  35. SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
  36. SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
  37. SQLite: 1.037
  38. SQLite Secure: 1.023
  39. Slowdown: -1.305
  40.  
  41. Test 4: 100 SELECTs on a string comparison
  42.  
  43. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
  44. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
  45. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';
  46. ... 94 lines omitted
  47. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';
  48. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
  49. SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
  50. SQLite: 3.207
  51. SQLite Secure: 3.229
  52. Slowdown: 0.706
  53.  
  54. Test 5: Creating an index
  55.  
  56. CREATE INDEX i2a ON t2(a);
  57. CREATE INDEX i2b ON t2(b);
  58. SQLite: 0.264
  59. SQLite Secure: 0.424
  60. Slowdown: 60.558
  61.  
  62. Test 6: 5000 SELECTs with an index
  63.  
  64. SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
  65. SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
  66. SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;
  67. ... 4994 lines omitted
  68. SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
  69. SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
  70. SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;
  71. SQLite: 1.010
  72. SQLite Secure: 1.094
  73. Slowdown: 8.237
  74.  
  75. Test 7: 1000 UPDATEs without an index
  76.  
  77. BEGIN;
  78. UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
  79. UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;
  80. ... 996 lines omitted
  81. UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
  82. UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
  83. COMMIT;
  84. SQLite: 0.344
  85. SQLite Secure: 0.454
  86. Slowdown: 31.951
  87.  
  88. Test 8: 25000 UPDATEs with an index
  89.  
  90. BEGIN;
  91. UPDATE t2 SET b=271822 WHERE a=1;
  92. UPDATE t2 SET b=28304 WHERE a=2;
  93. ... 24996 lines omitted
  94. UPDATE t2 SET b=442549 WHERE a=24999;
  95. UPDATE t2 SET b=423958 WHERE a=25000;
  96. COMMIT;
  97. SQLite: 1.230
  98. SQLite Secure: 1.422
  99. Slowdown: 15.605
  100.  
  101. Test 9: 25000 text UPDATEs with an index
  102.  
  103. BEGIN;
  104. UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;
  105. UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;
  106. ... 24996 lines omitted
  107. UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;
  108. UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;
  109. COMMIT;
  110. SQLite: 0.980
  111. SQLite Secure: 1.303
  112. Slowdown: 33.032
  113.  
  114. Test 10: INSERTs from a SELECT
  115.  
  116. BEGIN;
  117. INSERT INTO t1 SELECT * FROM t2;
  118. INSERT INTO t2 SELECT * FROM t1;
  119. COMMIT;
  120. SQLite: 2.935
  121. SQLite Secure: 1.794
  122. Slowdown: -38.880
  123.  
  124. Test 11: DELETE without an index
  125.  
  126. DELETE FROM t2 WHERE c LIKE '%fifty%';
  127. SQLite: 2.081
  128. SQLite Secure: 1.745
  129. Slowdown: -16.174
  130.  
  131. Test 12: DELETE with an index
  132.  
  133. DELETE FROM t2 WHERE a>10 AND a<20000;
  134. SQLite: 0.733
  135. SQLite Secure: 0.896
  136. Slowdown: 22.144
  137.  
  138. Test 13: A big INSERT after a big DELETE
  139.  
  140. INSERT INTO t2 SELECT * FROM t1;
  141. SQLite: 1.570
  142. SQLite Secure: 1.560
  143. Slowdown: -0.603
  144.  
  145. Test 14: A big DELETE followed by many small INSERTs
  146.  
  147. BEGIN;
  148. DELETE FROM t1;
  149. INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');
  150. ... 2997 lines omitted
  151. INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');
  152. INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');
  153. COMMIT;
  154. SQLite: 0.095
  155. SQLite Secure: 0.227
  156. Slowdown: 139.423
  157.  
  158. Test 15: DROP TABLE
  159.  
  160. DROP TABLE t1;
  161. DROP TABLE t2;
  162. SQLite: 0.066
  163. SQLite Secure: 0.211
  164. Slowdown: 218.260
Add Comment
Please, Sign In to add comment