On Tuesday, 28 January, 2020 23:42, Peng Yu wrote:
>I have two python programs using sqlite3. They function the same,
>except the following.
I presume this means you are using the standard (as in included with the
standard Python distribution) sqlite3 module? There are other ways to use
SQLite3 (the database) from Python, the sqlite3 (pysqlite2) wrapper being only
one of them. What are the statements being executed? Are you using "magical"
mode for the wrapper (what is the isolation_level set to on the sqlite3.connect
call).
>In the first, execute() is called in batches and then commit() is
>called following them. In the second, commit() is called after each
>execute(). It seems that the second case is faster (I can not separate
>my code in a self-contained test case to show here).
>This is counterintuitive. I thought the first should be faster.
>Is it expected that the 2nd case should be slightly faster?
The first case (batching multiple inserts in a single transaction) should be
faster. On the below test disk I/O rate exceeded 100 MB/s for wal mode.
-//- insertspeed.py -//-
import sqlite3
import sys
import time
db = sqlite3.connect('test.db')
db.executescript('create table if not exists x(x)')
records = 100
print(sys.version)
print('sqlite3 wrapper version', sqlite3.version, 'using library version',
sqlite3.sqlite_version)
print()
for mode in ['wal', 'delete']:
print('Database mode', mode)
print(' ')
print(' RecordsBatch Seconds')
print(' ')
for batchsize in [100, 10, 1, 1000, 100, 10, 1]:
db.executescript('pragma journal_mode=delete')
db.executescript('delete from x')
db.executescript('vacuum')
db.execute('pragma journal_mode=%s' % mode)
t = time.time()
for i in range(records):
db.execute('insert into x values (?)', (i,))
if i % batchsize == 0:
db.commit()
db.commit()
print('%8d %8d %8.3f' % (records, batchsize, time.time() - t))
print(' ')
-//--//-
3.8.1 (tags/v3.8.1:1b293b6, Dec 18 2019, 23:11:46) [MSC v.1916 64 bit (AMD64)]
sqlite3 wrapper version 2.6.0 using library version 3.31.1
Database mode wal
RecordsBatch Seconds
100 1002.255
100 102.348
10012.344
100 10002.453
100 1003.355
100 108.856
1001 64.167
Database mode delete
RecordsBatch Seconds
100 1002.215
100 102.460
10013.320
100 1000 12.099
100 100 96.896
I couldn't wait for the smaller batches to complete in journal_mode delete. I
would expect them to take a looong time ...
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
