On Tuesday, 28 January, 2020 23:42, Peng Yu <pengyu...@gmail.com> 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 = 1000000
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(' Records Batch Seconds')
print('-------- -------- --------')
for batchsize in [1000000, 100000, 10000, 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
-------- -------- --------
Records Batch Seconds
-------- -------- --------
1000000 1000000 2.255
1000000 100000 2.348
1000000 10000 2.344
1000000 1000 2.453
1000000 100 3.355
1000000 10 8.856
1000000 1 64.167
-------- -------- --------
Database mode delete
-------- -------- --------
Records Batch Seconds
-------- -------- --------
1000000 1000000 2.215
1000000 100000 2.460
1000000 10000 3.320
1000000 1000 12.099
1000000 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
