SQLite Forum

Memory spike with zeroblob

Memory spike with zeroblob

(1) By anonymous on 2020-11-03 12:36:45 [link] [source]


I have this sample python code that I run under x64 3.8.6 on Windows

import sqlite3
file = "BLABLABLABLBLA_sql.bak"
db = sqlite3.connect('generic.bckp')
cursor = db.cursor()
cursor.execute('CREATE TABLE t (id INTEGER PRIMARY KEY, b BLOB, p BLOB)')
cursor.execute('INSERT INTO t VALUES(?, zeroblob(?), ?)', [1, 100256716, file.encode()])

Problem is the memory profiler says it allocates about 200 Mb of memory

If I remove either the zeroblob or the binary encoded file name - all is well

Momchil Bozhinov
(posting anonymously due to being lazy)

(2) By Richard Hipp (drh) on 2020-11-03 14:56:13 in reply to 1 [source]

SQLite tries to optimize memory usage by not allocating zero bytes that occur at the end of a record. So a zeroblob() at the end of a record does not require a memory allocation. However, you have a few additional non-zero bytes that follow your big zeroblob, and so SQLite is compelled to allocate space for the entire thing.

Much less memory would be required if you exchange the order of the b and p columns.

(3) By anonymous on 2020-11-03 15:34:44 in reply to 2 [link] [source]

Thank you for this. It did help
So keep the huge blob at the end of it. Got it.

Would have been nice to get some sort of a notice or at least note somewhere in the zeroblob docs
I use apsw so that was my first stop, then the python sqlite implementation before I ended up here

(4) By anonymous on 2020-11-03 15:37:30 in reply to 2 [link] [source]

makes sense though cause this is a flat file db but it can be zeroed with much less memory. I mean twice the size of the allocation...