SQLite Forum

Slow update table with large BLOB issue(solution)
Login
I know best practice to work with large BLOB is to keep BLOB in second table and connect by key ID. Problem I have table, which is not created by me. I found way to update table with large BLOB fast, only not sure if this increasing updating speed result of other issue or acceptable method.

So table:
CREATE TABLE IF NOT EXISTS Test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    modified DATETIME,
    contentLARGE BLOB
)

First slow update method, because of contentLARGE BLOB:
UPDATE Test SET name=?,  modified=CURRENT_TIMESTAMP WHERE id=?

Second fast:
WITH RECURSIVE tc( i ) AS ( select id from Test where id = ?)
UPDATE Test SET name=?, modified=CURRENT_TIMESTAMP WHERE id in tc

My question why second method fast and first slow? Is it acceptable use second method for update and if so, why first direct update method not using similar technique?