Perl vs PHP - [3] :: Web-программирование :: Компьютерный форум Ru.Board

Если кому интересно тест баз в перле, взял с сайта www.sqlite.org
Не совсем по теме, но тоже интересно.

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: 4.373
MySQL: 0.114
SQLite 2.7.6: 13.061
SQLite 2.7.6 (nosync): 0.22

Test 2: 25000 INSERTs in a transaction
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');
COMMIT;

PostgreSQL: 4.900
MySQL: 2.184
SQLite 2.7.6: 0.914
SQLite 2.7.6 (nosync): 0.757

Test 3: 25000 INSERTs into an indexed table
BEGIN;
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));
CREATE INDEX i3 ON t3(c);
... 24998 lines omitted
INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');
INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');
COMMIT;

PostgreSQL: 8.175
MySQL: 3.197
SQLite 2.7.6: 1.555
SQLite 2.7.6 (nosync): 1.402

Test 4: 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: 3.629
MySQL: 2.760
SQLite 2.7.6: 2.494
SQLite 2.7.6 (nosync): 2.526

Test 5: 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: 13.409
MySQL: 4.640
SQLite 2.7.6: 3.362
SQLite 2.7.6 (nosync): 3.372

Test 6: Creating an index
CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);
PostgreSQL: 0.381
MySQL: 0.318
SQLite 2.7.6: 0.777
SQLite 2.7.6 (nosync): 0.659

Test 7: 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: 4.614
MySQL: 1.270
SQLite 2.7.6: 1.121
SQLite 2.7.6 (nosync): 1.162

Test 8: 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.739
MySQL: 8.410
SQLite 2.7.6: 0.637
SQLite 2.7.6 (nosync): 0.638

Test 9: 25000 UPDATEs with an index
BEGIN;
UPDATE t2 SET b=468026 WHERE a=1;
UPDATE t2 SET b=121928 WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET b=35065 WHERE a=24999;
UPDATE t2 SET b=347393 WHERE a=25000;
COMMIT;

PostgreSQL: 18.797
MySQL: 8.134
SQLite 2.7.6: 3.520
SQLite 2.7.6 (nosync): 3.104

Test 10: 25000 text UPDATEs with an index
BEGIN;
UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;
UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;
UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;
COMMIT;

PostgreSQL: 48.133
MySQL: 6.982
SQLite 2.7.6: 2.408
SQLite 2.7.6 (nosync): 1.725

Test 11: INSERTs from a SELECT
BEGIN;
INSERT INTO t1 SELECT b,a,c FROM t2;
INSERT INTO t2 SELECT b,a,c FROM t1;
COMMIT;
PostgreSQL: 61.364
MySQL: 1.537
SQLite 2.7.6: 2.787
SQLite 2.7.6 (nosync): 1.599

Test 12: DELETE without an index
DELETE FROM t2 WHERE c LIKE '%fifty%';
PostgreSQL: 1.509
MySQL: 0.975
SQLite 2.7.6: 4.004
SQLite 2.7.6 (nosync): 0.560

Test 13: DELETE with an index
DELETE FROM t2 WHERE a>10 AND a<20000;
PostgreSQL: 1.316
MySQL: 2.262
SQLite 2.7.6: 2.068
SQLite 2.7.6 (nosync): 0.752

Test 14: A big INSERT after a big DELETE
INSERT INTO t2 SELECT * FROM t1;
PostgreSQL: 13.168
MySQL: 1.815
SQLite 2.7.6: 3.210
SQLite 2.7.6 (nosync): 1.485

Test 15: A big DELETE followed by many small INSERTs
BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');
... 11997 lines omitted
INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');
INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');
COMMIT;

PostgreSQL: 4.556
MySQL: 1.704
SQLite 2.7.6: 0.618
SQLite 2.7.6 (nosync): 0.406

Test 16: DROP TABLE
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
PostgreSQL: 0.135
MySQL: 0.015
SQLite 2.7.6: 0.939
SQLite 2.7.6 (nosync): 0.254

Модерирует : Cheery
Версия для печати • Подписаться • Добавить в закладки
Страницы: 1 2 3 4