Re: [SQLite] Use of db:exec cause 5 second delay

[David Favro <lua@...>]

On 01/29/2014 11:41 AM, Gilles Ganault wrote:

> I noticed that the "db:exec" part of the following script waits for
> about 5s when running on a Linux appliance:

> -- stalls for about 5s
> db:exec[[
>    CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
>    INSERT INTO test VALUES (NULL, 'Hello World');
>    INSERT INTO test VALUES (NULL, 'Hello Lua');
>    INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
> ]]

Wrap all that into one transaction, like:

db:exec[[
   BEGIN TRANSACTION;
   CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
   INSERT INTO test VALUES (NULL, 'Hello World');
   INSERT INTO test VALUES (NULL, 'Hello Lua');
   INSERT INTO test VALUES (NULL, 'Hello Sqlite3');
   COMMIT;
]]

SQLite3 by default makes each statement into a separate transaction, and 
syncs to physical disk (not filesystem buffers) at each commit.  Still, 5 
seconds for 4 statements is a bit extreme, so I'm guessing that your 
appliance has slow-write storage, like maybe flash.