| View previous topic :: View next topic |
| Author |
Message |
Lady Abagail
Joined: 27 Jul 2006
Posts: 17
|
 Posted: Sat Jul 29, 2006 13:09 Post subject: COMMIT transaction |
 |
|
| Papillon wrote: | | Great! Please keep in mind that SQLite gets relatively slow if it has to work without an open transaction. If you do not BEGIN somewhere later on, every write operation is immediately written to the disk, which is slow. |
Okay now that we've addressed the ATTACH statement and how to use it in NWScript without changing the existing NWNX code, I have a few new questions about SQL statements. Right now Im executing a "COMMIT TRANSACTION;" before every ATTACH statement, and adding a BEGIN statement directly after.
My questions here are:
- Does the BEGIN statement have any variables that should be executed? (ie. DEFERRED, IMMEDIATE, EXCLUSIVE).
- What is the syntax used by NWNX2 to BEGIN when it is first opened?
- Does the transaction for the BEGIN statement need a transaction name? (ie. "BEGIN TRANSACTION name;").
- If no transaction name is called does it default to something?
Thanks so much for your support!
SHOUT OUT to Tenkawa and Papillon for their help with the ATTACH statement! 
_________________
"Pudding can't fill the emptiness inside me...but it will help!" |
|
| Back to top |
|
 |
Acrodania
Joined: 02 Jan 2005
Posts: 208
|
| Posted: Sat Jul 29, 2006 17:44 Post subject: |
|
|
SQLite only supports a single transaction so parts 3-4 are irrelevant  |
|
| Back to top |
|
|
Lady Abagail
Joined: 27 Jul 2006
Posts: 17
|
| Posted: Sat Jul 29, 2006 19:16 Post subject: |
|
|
| Acrodania wrote: | | SQLite only supports a single transaction so parts 3-4 are irrelevant |
So a single expression such as:
| Code: |
SQLExecDirect("BEGIN TRANSACTION;");
|
Will create an open transaction until another COMMIT is called even if a COMMIT has already been called on module load? SQLite says the simply making another query opens a new transaction, but I sorta got the impression that by calling a COMMIT, all writes sent to SQLite automatically wrote to disk without calling a new transaction.
_________________
"Pudding can't fill the emptiness inside me...but it will help!" |
|
| Back to top |
|
|
Acrodania
Joined: 02 Jan 2005
Posts: 208
|
| Posted: Sat Jul 29, 2006 23:53 Post subject: |
|
|
Common use would be to Commit, then immediately open a new transaction. A few minutes later Commit again, then open a new one.
That way you can take advantage of the speed of SQLite running with transactions but have less likelyhood of having to recover a lot of information after a crash.
If you immediately call a Commit but don't open another transaction then everything is written to disk when it happens. This slows writes down by almost 10x. Its better, especially on a loaded server, to do an occasional write during non-load times (like a quick check to see if anyone is in combat). |
|
| Back to top |
|
|
Lady Abagail
Joined: 27 Jul 2006
Posts: 17
|
| Posted: Sun Jul 30, 2006 0:23 Post subject: |
|
|
| Acrodania wrote: | Common use would be to Commit, then immediately open a new transaction. A few minutes later Commit again, then open a new one.
That way you can take advantage of the speed of SQLite running with transactions but have less likelyhood of having to recover a lot of information after a crash.
If you immediately call a Commit but don't open another transaction then everything is written to disk when it happens. This slows writes down by almost 10x. Its better, especially on a loaded server, to do an occasional write during non-load times (like a quick check to see if anyone is in combat). |
Right now I have the SQL functions calling a COMMIT before creating/attaching/detaching a database and calling a BEGIN directly after. Since new databases and tables will be created from PC data, whenever a PC logs into the server there will be a COMMIT and a BEGIN called. That by itself will ensure a load of disk writes in a single gaming session before even writing any timed functions, or giving DM/Admins scripted items to do so (which I will probably do). Furthermore, player databases will be accessible by DMs in-game via scripted item w'dialog, which would call another COMMIT and BEGIN to open the database for the DM to peruse.
_________________
"Pudding can't fill the emptiness inside me...but it will help!" |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Sun Jul 30, 2006 9:52 Post subject: |
|
|
Have you done any performance measures on the ATTACH command ?
_________________
Papillon |
|
| Back to top |
|
|
Lady Abagail
Joined: 27 Jul 2006
Posts: 17
|
| Posted: Sun Jul 30, 2006 18:00 Post subject: |
|
|
| Papillon wrote: | | Have you done any performance measures on the ATTACH command ? |
Not yet, I am currently designing my database set-up. Once it is executable I will put up a test server and log in with various characters to generate performance data. I will share the results with the community here.
_________________
"Pudding can't fill the emptiness inside me...but it will help!" |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Jul 31, 2006 1:26 Post subject: |
|
|
| Lady Abagail wrote: | | Papillon wrote: | | Have you done any performance measures on the ATTACH command ? |
Not yet, I am currently designing my database set-up. Once it is executable I will put up a test server and log in with various characters to generate performance data. I will share the results with the community here. |
In the context of NWNX database performance hits are going to be negligable. ATTACH might have a hit when it opens/creates a file on disk, but after that, you'll see benchmark type performance.
TRANSACTIONs are designed to maintain consistancy with your data in a concurrent environment. Hmmm NWNX is one user -- you might have an Admin as well. Very little contention.
Having many COMMITs can have some advantages. The transaction log doesn't need to grow to very large sizes. You only have a few open transactions to worry about at any one time. And should your server crash - the recovery of the database is quicker with a smaller transaction log to process. - but don't have too many or writing to the transaction log becomes a bottleneck.
I believe SQLLite flushes to disk on every commit. This can be costly but shouldn't be an issue unless a flush involves writing to multiple DB files at the same time - otherwise it could be a huge hit with seek-times. (Defrag often)
Cheers
Gryphyn |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
