[Home] [Downloads] [Search] [Help/forum]

Gammon Software Solutions forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  Lua
. . -> [Subject]  SQLite is slow with inserts?
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?
(New message)
Subject: SQLite is slow with inserts?
Name:
Your forum user name.
Register forum user name
Password:
Your forum password.
Forgotten password?
Message:
Message to be posted (in English, please).
Forum codes:
Check this if your message uses 'forum codes' or templates (auto-detected for new posts).
Forum codes Templates

Save this message ...

Subject review (reverse sequence)

Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Tue 30 Sep 2008 03:47 AM (UTC)  [ quote  ]
Message
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.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Tue 30 Sep 2008 12:49 AM (UTC)  [ quote  ]
Message
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.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top
Posted by WillFa   USA  (517 posts)  [Biography] bio
Date Mon 29 Sep 2008 10:13 PM (UTC)  [ quote  ]
Message
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? :)
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Mon 29 Sep 2008 02:59 PM (UTC)  [ quote  ]
Message
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?
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Mon 29 Sep 2008 03:25 AM (UTC)  [ quote  ]
Message
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. :-)
- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top
Posted by Artel   USA  (63 posts)  [Biography] bio
Date Mon 29 Sep 2008 02:37 AM (UTC)  [ quote  ]
Message
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?
Artel.
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

3,336 views.

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Go to topic:           Search the forum

[Go to top] top

[Home]

Written by Nick Gammon - 5K

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( http://www.gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Internet Contents Rating Association (ICRA) - 2K]    [Web site powered by FutureQuest.Net]