Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are
spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the
password reset link.
Due to spam on this forum, all posts now need moderator approval.
Entire forum
➜ MUSHclient
➜ Lua
➜ SQLite is slow with inserts?
|
SQLite is slow with inserts?
|
It is now over 60 days since the last post. This thread is closed.
Refresh page
| Posted by
| Artel
USA (65 posts) Bio
|
| Date
| Mon 29 Sep 2008 02:37 AM (UTC) |
| 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. | | Top |
|
| Posted by
| Nick Gammon
Australia (23,158 posts) Bio
Forum Administrator |
| Date
| Reply #1 on Mon 29 Sep 2008 03:25 AM (UTC) |
| 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 | | Top |
|
| Posted by
| David Haley
USA (3,881 posts) Bio
|
| Date
| Reply #2 on Mon 29 Sep 2008 02:59 PM (UTC) |
| 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 | | Top |
|
| Posted by
| WillFa
USA (525 posts) Bio
|
| Date
| Reply #3 on Mon 29 Sep 2008 10:13 PM (UTC) |
| 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? :) | | Top |
|
| Posted by
| David Haley
USA (3,881 posts) Bio
|
| Date
| Reply #4 on Tue 30 Sep 2008 12:49 AM (UTC) |
| 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 | | Top |
|
| Posted by
| Nick Gammon
Australia (23,158 posts) Bio
Forum Administrator |
| Date
| Reply #5 on Tue 30 Sep 2008 03:47 AM (UTC) |
| 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 | | 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.
21,979 views.
It is now over 60 days since the last post. This thread is closed.
Refresh page
![[Go to top]](/images/gototop.gif)
top