On 3/6/16, R Smith <rsmith at rsweb.co.za> wrote:
>
> On 2016/03/06 10:00 AM, Keith Medcalf wrote:
>
> mind posting the DB or schema and the
> query(ies) used during the benchmark tests?
>
He said "using --size 500" which implies that he was running the
speedtest1.exe utility program, which is part of the SQLite source
tree. "make speedtest1". It starts with an empty database file,
creates the schema, the populates the file, and performs many common
operations. I tried to design the sequence of operations to be
similar to the way I see SQLite being used in typical applications.
You can get a complete listing of the SQL by running "speedtest1
--sqlonly --size 500" command. Use --help to get a complete list of
options.
PRAGMA threads=0;
/* 100 - 250000 INSERTs into table with no index..................... */
BEGIN;
CREATE TABLE t1(a INTEGER , b INTEGER , c TEXT );
INSERT INTO t1 VALUES(?1,?2,?3); -- 250000 times;
COMMIT;
/* 110 - 250000 ordered INSERTS with one index/PK.................... */
BEGIN;
CREATE TABLE t2(a INTEGER UNIQUE, b INTEGER , c TEXT );
INSERT INTO t2 VALUES(?1,?2,?3); -- 250000 times;
COMMIT;
/* 120 - 250000 unordered INSERTS with one index/PK.................. */
BEGIN;
CREATE TABLE t3(a INTEGER UNIQUE, b INTEGER , c TEXT );
INSERT INTO t3 VALUES(?1,?2,?3); -- 250000 times;
COMMIT;
/* 130 - 25 SELECTS, numeric BETWEEN, unindexed...................... */
BEGIN;
SELECT count(*), avg(b), sum(length(c)) FROM t1
WHERE b BETWEEN ?1 AND ?2; -- 25 times;
COMMIT;
/* 140 - 10 SELECTS, LIKE, unindexed................................. */
BEGIN;
SELECT count(*), avg(b), sum(length(c)) FROM t1
WHERE c LIKE ?1; -- 10 times;
COMMIT;
/* 142 - 10 SELECTS w/ORDER BY, unindexed............................ */
BEGIN;
SELECT a, b, c FROM t1 WHERE c LIKE ?1
ORDER BY a; -- 10 times;
COMMIT;
/* 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.................. */
BEGIN;
SELECT a, b, c FROM t1 WHERE c LIKE ?1
ORDER BY a LIMIT 10; -- 10 times;
COMMIT;
/* 150 - CREATE INDEX five times..................................... */
BEGIN;
CREATE UNIQUE INDEX t1b ON t1(b);
CREATE INDEX t1c ON t1(c);
CREATE UNIQUE INDEX t2b ON t2(b);
CREATE INDEX t2c ON t2(c DESC);
CREATE INDEX t3bc ON t3(b,c);
COMMIT;
/* 160 - 50000 SELECTS, numeric BETWEEN, indexed..................... */
BEGIN;
SELECT count(*), avg(b), sum(length(c)) FROM t1
WHERE b BETWEEN ?1 AND ?2; -- 50000 times;
COMMIT;
/* 161 - 50000 SELECTS, numeric BETWEEN, PK.......................... */
BEGIN;
SELECT count(*), avg(b), sum(length(c)) FROM t2
WHERE a BETWEEN ?1 AND ?2; -- 50000 times;
COMMIT;
/* 170 - 50000 SELECTS, text BETWEEN, indexed........................ */
BEGIN;
SELECT count(*), avg(b), sum(length(c)) FROM t1
WHERE c BETWEEN ?1 AND (?1||'~'); -- 50000 times;
COMMIT;
/* 180 - 250000 INSERTS with three indexes........................... */
BEGIN;
CREATE TABLE t4(
a INTEGER UNIQUE,
b INTEGER ,
c TEXT
);
CREATE INDEX t4b ON t4(b);
CREATE INDEX t4c ON t4(c);
INSERT INTO t4 SELECT * FROM t1;
COMMIT;
/* 190 - DELETE and REFILL one table................................. */
DELETE FROM t2;
INSERT INTO t2 SELECT * FROM t1;
/* 200 - VACUUM...................................................... */
VACUUM;
/* 210 - ALTER TABLE ADD COLUMN, and query........................... */
ALTER TABLE t2 ADD COLUMN d DEFAULT 123;
SELECT sum(d) FROM t2;
/* 230 - 50000 UPDATES, numeric BETWEEN, indexed..................... */
BEGIN;
UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- 50000 times;
COMMIT;
/* 240 - 250000 UPDATES of individual rows........................... */
BEGIN;
UPDATE t2 SET d=b*3 WHERE a=?1; -- 250000 times;
COMMIT;
/* 250 - One big UPDATE of the whole 250000-row table................ */
UPDATE t2 SET d=b*4;
/* 260 - Query added column after filling............................ */
SELECT sum(d) FROM t2;
/* 270 - 50000 DELETEs, numeric BETWEEN, indexed..................... */
BEGIN;
DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- 50000 times;
COMMIT;
/* 280 - 250000 DELETEs of individual rows........................... */
BEGIN;
DELETE FROM t3 WHERE a=?1; -- 250000 times;
COMMIT;
/* 290 - Refill two 250000-row tables using REPLACE.................. */
REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1;
REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1;
/* 300 - Refill a 250000-row table using (b&1)==(a&1)................ */
DELETE FROM t2;
INSERT INTO t2(a,b,c)
SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);
INSERT INTO t2(a,b,c)
SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);
/* 310 - 50000 four-ways joins....................................... */
BEGIN;
SELECT t1.c FROM t1, t2, t3, t4
WHERE t4.a BETWEEN ?1 AND ?2
AND t3.a=t4.b
AND t2.a=t3.b
AND t1.c=t2.c;
COMMIT;
/* 320 - subquery in result set...................................... */
SELECT sum(a), max(c),
avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)
FROM t1 WHERE rowid<?1;
/* 980 - PRAGMA integrity_check...................................... */
PRAGMA integrity_check;
/* 990 - ANALYZE..................................................... */
ANALYZE;
--
D. Richard Hipp
drh at sqlite.org
