SQLite is slow with inserts?

Posted by Artel on Mon 29 Sep 2008 02:37 AM — 6 posts, 26,705 views.

Artel USA Mon 29 Sep 2008 02:37 AM #0

I'm working on a script that may need be able to insert up to around 300 rows at a time. However, I'm getting a 5+ second hang when I try to insert 268 rows into SQLite. I've worked with MySQL in the past, and I don't recall it taking this long.

The 5+ second hang while the INSERTs run does hang all of MUSHclient. Granted, when I'm finished with the script, these INSERTs will only need to run once upon the first install. I'd like to speed it up, regardless, because I don't think it makes sense for it to take this long.

Does anyone have any ideas?

Nick Gammon Australia Forum Administrator Mon 29 Sep 2008 03:25 AM #1

As luck has it, I have been reading the Sqlite3 documentation about speed tests.

I can confirm that a test script for me did indeed take about 4 seconds to insert 300 records.

But there is a simple solution - transactions. If you wrap the whole set of inserts into a transaction it is much faster. For example:


start = os.time ()

assert (con:execute ("BEGIN TRANSACTION"))

for i = 1, 10000 do
 assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('player %i', 'class %i'); ]], i, i)
  ))
  
end

assert (con:execute ("COMMIT"))

fin = os.time ()

print ("time = ", os.difftime (fin , start)) --> 1

In my test the insert of 300 records seemed instantaneous, and in the script above it only took 1 second to insert 10,000 records.

So, transactions are your friend. :-)

David Haley USA Mon 29 Sep 2008 02:59 PM #2

Do you suppose that analogous to growing the array by 10k in size before inserting elements, rather than growing the array one at a time, ten thousand times?

WillFa USA Mon 29 Sep 2008 10:13 PM #3

I'd think it's more analogous to writing a 1 meg file, instead of writing, and then defragging, 1024 1k files. I'd guess with SqlLite most of the overhead is in file i/o. Don't thrash the disk, use a transaction.

I was curious to check it out, but I'm having a problem getting the lua-sql dll to compile. Could someone post a binary for it, please? :)

David Haley USA Tue 30 Sep 2008 12:49 AM #4

Oh, well, that's what I meant, except that when you do it with arrays that happens in memory but with files it happens on disk. But you're right that if it has to happen on disk every time too, it'll take a [i]lot[/i] longer -- transactions are probably buffered in memory.

Nick Gammon Australia Forum Administrator Tue 30 Sep 2008 03:47 AM #5

Binary at:

http://luaforge.net/frs/download.php/2685/luasql-2.1.1-sqlite3-win32-lua51.zip

As for the timing, if you read their page about how they handle a transaction there is a lot of stuff about creating a rollback file, writing to it, updating the database, deleting the rollback file. All of this stuff will have an overhead (eg. 300 file creates and 300 file deletes) so it isn't a big surprise it takes a few seconds. However in a single transaction it only has to be done once.