18
\$\begingroup\$

I've read most of the posts I could find on optimizing SQLite3 performance, such as:

Which are good, but they have not been updated for a few SQLite releases, so I went for "testing before believing". In the "testing" I became suspicious of whether my approach to benchmarking of the modes of using SQLite is correct? The reason for asking is that I get a much lower threshold difference in performance - which is slightly contrary to the posts I've read, where some report orders of magnitude in difference.

for example http://www.sqlite.org/pragma.html#pragma_journal_mode comments:

The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed.

But I cannot replicate the drop/increase in performance which should be "expected" from performing single transactions in different modes. So I wonder: Has SQLite3 evolved so much that these differences become a legend of the past? - or did I just miss something?

Update: Yes something was missing: The documentation states that:

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command.

This might not to be the case, as pysqlite appears cycles through the operation without executing a commit between "execute" statements. I don't know how to test for this?

Results summary

 Using SQLite 3.6.21 and pysqlite version 2.6.0 and python 2.7.5

summary overview of the test of sqlite3 operating modes.

Code

Update 22:26 31/05/2013: Generator added: Creates synthetic data for review if file is not available.

#!/usr/bin/python27
import sys
import csv
import time
import sqlite3
from datetime import time as Time # used only for generating data if file is absent
from random import randint # used only for generating data if file is absent

def get_the_data():

    msg = """
        Thanks to http://opendata.toronto.ca/TTC/routes/
        OpenData_TTC_Schedules.zip/stop_times.txt
        for the data.
        """
    print msg

    """ Returns a list with this:
    trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled
    19985180,6:15:00,6:15:00,14146,1,,0,0,
    19985180,6:16:41,6:16:41,3807,2,,0,0,0.5355
    19985180,6:17:41,6:17:41,6904,3,,0,0,0.8543
    ...
    (many lines later)
    ...
    20064514,25:41:41,25:41:41,7531,65,,0,0,17.6094
    20064514,25:42:00,25:42:00,13118,66,,1,1,17.6794
    """

    data=[]
    start = time.clock()
    try:
        with open(r'D:\OpenData_TTC_Schedules\stop_times.txt') as fi:
            for row in csv.reader(fi, delimiter=','):
                data.append(tuple(row)) # SQLite's "executemany()" needs tuples.
        data.pop(0) # get rid of headers.
    except IOError:
        print "\tfile not found - generating data..."
        data=syntheticData()

    end = time.clock()
    wall_time = end - start

    print "\tFetched %d lines of data, or %.3f Mb, in %.3f seconds" %(len(data), sys.getsizeof(data)/float(10**6), wall_time)
    print "\t(Reading csv from disk = %d rows per second)." %(len(data)/wall_time)
    return data

def syntheticData(length=4*10**5):
    alist=[]
    for i in range(length):
        trip_id = 19985180+i
        arrival_time = Time(randint(0,23),randint(0,59),randint(0,59)).isoformat()
        departure_time = Time(randint(0,23),randint(0,59),randint(0,59)).isoformat()
        stop_id = randint(1,15000)
        stop_sequence = randint(1,66)
        stop_headsign = ''
        pickup_type = randint(0,1)
        drop_off_type = randint(0,1)
        shape_dist_traveled = round(float(randint(5000,25000)/1000),4)
        row=tuple([trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled])
        alist.append(row)
    return alist

def transaction(data, DB, SQL_create_table, SQL, PRAGMA="ON", JOURNAL="", BATCH=True):

    con = sqlite3.connect(DB)
    cur = con.cursor()

    if PRAGMA=="OFF":
        cur.execute("PRAGMA synchronous = OFF")
    if JOURNAL!="":
        cur.execute("PRAGMA journal_mode = %s" %(JOURNAL))

    cur.execute(SQL_create_table) # Create the table.

    start = time.clock()
    if BATCH==True:
        cur.executemany(SQL, data) # Do the insert test
        # Save (commit) the changes
        con.commit() ###
    else:
        for row in data[:100000]: # We have patience for 100k rows.
            cur.execute(SQL, row)
            # Save (commit) the changes
            con.commit() ###
            if time.clock()-start > 300:
                break
    end = time.clock()

    cur.execute("SELECT IFNULL(MAX(id),1) FROM TTC;")
    rows=cur.fetchone()[0]

    wall_time = (end-start)
    if JOURNAL=="":
        JOURNAL="(default)"
    if BATCH==True:
        MODE="EXECUTEMANY"
    else:
        MODE="EXECUTE"
    print "\t%.2f rows per second with PRAGMA=%s and JOURNAL=%s using %s in %s" %(int(rows/wall_time),PRAGMA, JOURNAL, MODE, DB)

    SQL = "DROP TABLE IF EXISTS TTC;" # wipe the junk
    cur.execute(SQL)
    con.commit()
    con.close()

if __name__ == '__main__':

    data = get_the_data()

    print "\n\tUsing SQLite %s and pysqlite version %s" %(sqlite3.sqlite_version, sqlite3.version)

    SQL_create_table = "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, trip_id TEXT, arrival_time TEXT, departure_time TEXT, stop_id INTEGER, stop_sequence INTEGER, stop_headsign TEXT, pickup_type TEXT, drop_off_type TEXT, shape_dist_traveled REAL);"
    SQL_data_operation = "INSERT INTO TTC VALUES(Null,?,?,?,?,?,?,?,?,?)" # the Null keyword permits SQLite to autoincrement.

    DB = 'sqlite3_speedtest.db'
    print "\tSingle Transactions using EXECUTE To DISK"
    transaction(data, DB, SQL_create_table, SQL_data_operation, BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", BATCH=False) 
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF", BATCH=False)
    print "\tBatched Transactions using EXECUTEMANY To DISK"
    transaction(data, DB, SQL_create_table, SQL_data_operation)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF")

    DB = ':memory:'
    print "\tSingle Transactions using EXECUTE To MEMORY"
    transaction(data, DB, SQL_create_table, SQL_data_operation, BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", BATCH=False) 
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF", BATCH=False)
    print "\tBatched Transactions using EXECUTEMANY To MEMORY"
    transaction(data, DB, SQL_create_table, SQL_data_operation)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF")

    print "\tTest Completed"

Output

    Thanks to http://opendata.toronto.ca/TTC/routes/
    OpenData_TTC_Schedules.zip/stop_times.txt
    for the data.

    Fetched 4624024 lines of data, or 20.108 Mb, in 8.170 seconds
    (Reading csv from disk = 565958 rows per second).

    Using SQLite 3.6.21 and pysqlite version 2.6.0
    Single Transactions using EXECUTE To DISK
    289.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTE in sqlite3_speedtest.db
    134.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTE in sqlite3_speedtest.db
    136.00 rows per second with PRAGMA=ON and JOURNAL=DELETE using EXECUTE in sqlite3_speedtest.db
    157.00 rows per second with PRAGMA=ON and JOURNAL=TRUNCATE using EXECUTE in sqlite3_speedtest.db
    527.00 rows per second with PRAGMA=ON and JOURNAL=MEMORY using EXECUTE in sqlite3_speedtest.db
    137.00 rows per second with PRAGMA=ON and JOURNAL=WAL using EXECUTE in sqlite3_speedtest.db
    522.00 rows per second with PRAGMA=ON and JOURNAL=OFF using EXECUTE in sqlite3_speedtest.db
    564.00 rows per second with PRAGMA=OFF and JOURNAL=(default) using EXECUTE in sqlite3_speedtest.db
    560.00 rows per second with PRAGMA=OFF and JOURNAL=DELETE using EXECUTE in sqlite3_speedtest.db
    2145.00 rows per second with PRAGMA=OFF and JOURNAL=TRUNCATE using EXECUTE in sqlite3_speedtest.db
    5579.00 rows per second with PRAGMA=OFF and JOURNAL=MEMORY using EXECUTE in sqlite3_speedtest.db
    563.00 rows per second with PRAGMA=OFF and JOURNAL=WAL using EXECUTE in sqlite3_speedtest.db
    11311.00 rows per second with PRAGMA=OFF and JOURNAL=OFF using EXECUTE in sqlite3_speedtest.db
    Batched Transactions using EXECUTEMANY To DISK
    192030.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTEMANY in sqlite3_speedtest.db
    164718.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTEMANY in sqlite3_speedtest.db
    177257.00 rows per second with PRAGMA=ON and JOURNAL=DELETE using EXECUTEMANY in sqlite3_speedtest.db
    167466.00 rows per second with PRAGMA=ON and JOURNAL=TRUNCATE using EXECUTEMANY in sqlite3_speedtest.db
    181955.00 rows per second with PRAGMA=ON and JOURNAL=MEMORY using EXECUTEMANY in sqlite3_speedtest.db
    166388.00 rows per second with PRAGMA=ON and JOURNAL=WAL using EXECUTEMANY in sqlite3_speedtest.db
    212319.00 rows per second with PRAGMA=ON and JOURNAL=OFF using EXECUTEMANY in sqlite3_speedtest.db
    246463.00 rows per second with PRAGMA=OFF and JOURNAL=(default) using EXECUTEMANY in sqlite3_speedtest.db
    245461.00 rows per second with PRAGMA=OFF and JOURNAL=DELETE using EXECUTEMANY in sqlite3_speedtest.db
    243850.00 rows per second with PRAGMA=OFF and JOURNAL=TRUNCATE using EXECUTEMANY in sqlite3_speedtest.db
    246533.00 rows per second with PRAGMA=OFF and JOURNAL=MEMORY using EXECUTEMANY in sqlite3_speedtest.db
    245036.00 rows per second with PRAGMA=OFF and JOURNAL=WAL using EXECUTEMANY in sqlite3_speedtest.db
    245244.00 rows per second with PRAGMA=OFF and JOURNAL=OFF using EXECUTEMANY in sqlite3_speedtest.db
    Single Transactions using EXECUTE To MEMORY
    85012.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTE in :memory:
    85393.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTE in :memory:
    85461.00 rows per second with PRAGMA=ON and JOURNAL=DELETE using EXECUTE in :memory:
    85249.00 rows per second with PRAGMA=ON and JOURNAL=TRUNCATE using EXECUTE in :memory:
    85315.00 rows per second with PRAGMA=ON and JOURNAL=MEMORY using EXECUTE in :memory:
    84864.00 rows per second with PRAGMA=ON and JOURNAL=WAL using EXECUTE in :memory:
    25705.00 rows per second with PRAGMA=ON and JOURNAL=OFF using EXECUTE in :memory:
    83249.00 rows per second with PRAGMA=OFF and JOURNAL=(default) using EXECUTE in :memory:
    85097.00 rows per second with PRAGMA=OFF and JOURNAL=DELETE using EXECUTE in :memory:
    84700.00 rows per second with PRAGMA=OFF and JOURNAL=TRUNCATE using EXECUTE in :memory:
    85441.00 rows per second with PRAGMA=OFF and JOURNAL=MEMORY using EXECUTE in :memory:
    84724.00 rows per second with PRAGMA=OFF and JOURNAL=WAL using EXECUTE in :memory:
    25521.00 rows per second with PRAGMA=OFF and JOURNAL=OFF using EXECUTE in :memory:
    Batched Transactions using EXECUTEMANY To MEMORY
    284543.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTEMANY in :memory:
    285171.00 rows per second with PRAGMA=ON and JOURNAL=(default) using EXECUTEMANY in :memory:
    285505.00 rows per second with PRAGMA=ON and JOURNAL=DELETE using EXECUTEMANY in :memory:
    285609.00 rows per second with PRAGMA=ON and JOURNAL=TRUNCATE using EXECUTEMANY in :memory:
    285056.00 rows per second with PRAGMA=ON and JOURNAL=MEMORY using EXECUTEMANY in :memory:
    285799.00 rows per second with PRAGMA=ON and JOURNAL=WAL using EXECUTEMANY in :memory:
    286359.00 rows per second with PRAGMA=ON and JOURNAL=OFF using EXECUTEMANY in :memory:
    286316.00 rows per second with PRAGMA=OFF and JOURNAL=(default) using EXECUTEMANY in :memory:
    285607.00 rows per second with PRAGMA=OFF and JOURNAL=DELETE using EXECUTEMANY in :memory:
    285506.00 rows per second with PRAGMA=OFF and JOURNAL=TRUNCATE using EXECUTEMANY in :memory:
    285671.00 rows per second with PRAGMA=OFF and JOURNAL=MEMORY using EXECUTEMANY in :memory:
    285624.00 rows per second with PRAGMA=OFF and JOURNAL=WAL using EXECUTEMANY in :memory:
    285707.00 rows per second with PRAGMA=OFF and JOURNAL=OFF using EXECUTEMANY in :memory:
    Test Completed
\$\endgroup\$
6
  • \$\begingroup\$ @JanneKarila : Synthetic data generator added in code in lines 48-62 :-) \$\endgroup\$
    – root-11
    Commented May 31, 2013 at 21:29
  • \$\begingroup\$ Commit each insert and you'll see differences in times. Now you don't commit anything. \$\endgroup\$
    – Janne Karila
    Commented Jun 2, 2013 at 17:43
  • \$\begingroup\$ Hi Janne, In the documentation docs.python.org/2/library/sqlite3.html#controlling-transactions it is stated that: "if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command." - So why would I add an additional commit operation if SQLite commits implicitly? Is that not just bad practice? \$\endgroup\$
    – root-11
    Commented Jun 2, 2013 at 18:18
  • \$\begingroup\$ In the part of code that you are timing, you only execute inserts. No implicit commit there. \$\endgroup\$
    – Janne Karila
    Commented Jun 2, 2013 at 18:24
  • \$\begingroup\$ Hi Janne, i added con.commit() straight after cur.execute/executemany and get 82119 rows/second on execute versus 297689 rows/second on executemany: factor of 1.00:3.62, which is far far away from 85 inserts-per-second in other posts (what I expected in terms of orders of magnitude). \$\endgroup\$
    – root-11
    Commented Jun 2, 2013 at 18:42

1 Answer 1

Reset to default
8
\$\begingroup\$

I have just had a similar experience using Another Python SQLite Wrapper (APSW), which is an alternative to pysqlite that tends to be a bit faster. The PRAGMA options make practically no difference on a database of ~2 million rows (6 columns, also doing some string formatting on the values being entered):

  • 753,347 rows/minute with all PRAGMAs at default (i.e. nothing set)
  • 764,967 rows/minute with:
    • synchronous = OFF
    • locking_mode = Exclusive
    • page_size = 4096
    • journal_mode = WAL

Tests were run three times, values averaged. I determined the PRAGMA values for each of these settings iteratively by testing a range of values, each of which was slightly faster, but the overall improvement is negligible.

Although not exactly relevant to your post (but relevant to people using Python with SQLite), one thing that makes a massive performance difference is generating table indexes after adding values. Including the time to create two indexes on the table after the values are inserted, the numbers above drop to:

  • 691,774 rows/minute with all PRAGMAs at default (i.e. nothing set)
  • 711,995 rows/minute with PRAGMAs set as described above

However, creating two indexes on the table before adding the values, and running the same tests as above, I get a ~7-fold reduction:

  • 119,005 rows/minute with all PRAGMAs at default (i.e. nothing set)
  • 114,209 rows/minute with PRAGMAs set as described above

This shows a speed decrease with the PRAGMAs set, however that could just be within the margin of error (due to disk loading, file fragmentation, etc...?) as I only ran this latter test once for each condition.

\$\endgroup\$
2
  • \$\begingroup\$ Hi is the 7 fold reduction conditional to the data being sorted or is it consistent even with random data? \$\endgroup\$
    – root-11
    Commented Jul 7, 2013 at 20:12
  • \$\begingroup\$ @BHM I do not have the ability to test this for the given dataset, unfortunately. Interesting question though! Furthermore, if the data is unsorted, what is the total time to sort manually then add then index, vs. add unsorted then index? \$\endgroup\$
    – StacyR
    Commented Jul 9, 2013 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.