Skip to content

Instantly share code, notes, and snippets.

@iffy
Created November 5, 2018 16:22
Show Gist options
  • Save iffy/c8ca8a852c0bd4e35dc027f7cb39f762 to your computer and use it in GitHub Desktop.
Save iffy/c8ca8a852c0bd4e35dc027f7cb39f762 to your computer and use it in GitHub Desktop.
Small, probably non-representative benchmark of JSON Changelog with SQLite https://blog.budgetwithbuckets.com/2018/08/27/sqlite-changelog.html
python testsqlite.py 10000 :memory: 0
6.65568494797 seconds to do 10000 records db=:memory:, triggers=False
6.66251087189 seconds to do 10000 records db=:memory:, triggers=False
6.69321203232 seconds to do 10000 records db=:memory:, triggers=False
python testsqlite.py 10000 :memory: 1
6.76699709892 seconds to do 10000 records db=:memory:, triggers=True
6.82708096504 seconds to do 10000 records db=:memory:, triggers=True
6.82913088799 seconds to do 10000 records db=:memory:, triggers=True
python testsqlite.py 10000 somefile 0 ; rm somefile
6.72162890434 seconds to do 10000 records db=somefile, triggers=False
6.71077203751 seconds to do 10000 records db=somefile, triggers=False
6.69169092178 seconds to do 10000 records db=somefile, triggers=False
python testsqlite.py 10000 somefile 1 ; rm somefile
6.76263403893 seconds to do 10000 records db=somefile, triggers=True
6.75209498405 seconds to do 10000 records db=somefile, triggers=True
6.85438203812 seconds to do 10000 records db=somefile, triggers=True
import sqlite3
import time
import sys
numrecords, dbname, triggers = sys.argv[1:]
numrecords = int(numrecords)
triggers = True if triggers == "1" else False
create_sql = [
"""
-- Data table
CREATE TABLE people (
id INTEGER PRIMARY KEY,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
name TEXT,
age INTEGER
)""",
"""
-- Change log table
CREATE TABLE change_log (
id INTEGER PRIMARY KEY,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
action TEXT,
table_name TEXT,
obj_id INTEGER,
oldvals TEXT
)
""",
]
trigger_sql = [
"""
-- Insert Trigger
CREATE TRIGGER people_track_insert
AFTER INSERT ON people
BEGIN
INSERT INTO change_log (action, table_name, obj_id)
VALUES ('INSERT', 'people', NEW.id);
END;
""",
"""
-- Update Trigger
CREATE TRIGGER people_track_update
AFTER UPDATE ON people
BEGIN
INSERT INTO change_log (action, table_name, obj_id, oldvals)
SELECT
'UPDATE', 'people', OLD.id, changes
FROM
(SELECT
json_group_object(col, oldval) AS changes
FROM
(SELECT
json_extract(value, '$[0]') as col,
json_extract(value, '$[1]') as oldval,
json_extract(value, '$[2]') as newval
FROM
json_each(
json_array(
json_array('id', OLD.id, NEW.id),
json_array('created', OLD.created, NEW.created),
json_array('name', OLD.name, NEW.name),
json_array('age', OLD.age, NEW.age)
)
)
WHERE oldval IS NOT newval
)
);
END;
""",
"""
-- Delete Trigger
CREATE TRIGGER people_track_delete
AFTER DELETE ON people
BEGIN
INSERT INTO change_log (action, table_name, obj_id, oldvals)
SELECT
'DELETE', 'people', OLD.id, changes
FROM
(SELECT
json_group_object(col, oldval) AS changes
FROM
(SELECT
json_extract(value, '$[0]') as col,
json_extract(value, '$[1]') as oldval,
json_extract(value, '$[2]') as newval
FROM
json_each(
json_array(
json_array('id', OLD.id, null),
json_array('created', OLD.created, null),
json_array('name', OLD.name, null),
json_array('age', OLD.age, null)
)
)
WHERE oldval IS NOT newval
)
);
END;
""",
]
db = sqlite3.connect(dbname)
for statement in create_sql:
db.execute(statement)
if triggers:
for statement in trigger_sql:
db.execute(statement)
c = db.cursor()
start = time.time()
for i in xrange(numrecords):
c.execute("INSERT INTO people (name, age) VALUES (?, ?)", (i, i))
c.execute("UPDATE people SET age=age+1 WHERE name=?", (i,))
end = time.time()
seconds = end-start
print "{seconds} seconds to do {numrecords} records db={dbname}, triggers={triggers}".format(**locals())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment