19 Dec, 2011, Omega wrote in the 1st comment:
Votes: 0
For the last year and a bit I have been using XML for my saved files, and recently decided to use a SQL database (something I have done in the past but had problems with)

I am wondering, what is the better approach, maintaining a constant connection to the SQL server, or close the connection after each query has been processed.

so open -> read|write -> process -> close,

or open -> read|write -> process -> wait for next query -> on-shutdown close

This is the question I have been pondering over, and I'm wondering what experiences other people using SQL have encountered. Whats the better method to use.
19 Dec, 2011, Tyche wrote in the 2nd comment:
Votes: 0
What RDBMS?
Regardless I would imagine regardless it's just as expensive as opening/closing a file.
So how expensive depends on your use of the database.
19 Dec, 2011, Omega wrote in the 3rd comment:
Votes: 0
Tyche said:
What RDBMS?
Regardless I would imagine regardless it's just as expensive as opening/closing a file.
So how expensive depends on your use of the database.


MySql to be exact there Tyche. My question was more, do I maintain a constant connection to the database itself and launch my queries, or do I close the connection to the Database between queries. Which is more expensive, keeping the connection, or closing it. People tend to say to close the connection because of problems of maintaining an open connection. (as time-outs and other issues such as security get brought into the discussion)

So ultimately I was asking what is the better solution. Keeping it open to ease the reconnect all the time, or open/close relative to the query.
19 Dec, 2011, plamzi wrote in the 4th comment:
Votes: 0
See this.

I personally choose to keep the connection open using an API pref var which I believe auto-resets the idle timeout with every query. This gives me the best of both worlds. Because pretty much everything is saved to the db, there's no telling when the mud will fire a dozen queries within a second, and keeping alive will pay off. On the other hand, at slow times the timeout may kick in and connection will close.

But I'm not at all worried about keeping a connection open for the game server–it is at most just one client connection, albeit intensive at times, and its db is on localhost. I would be much more worried if I was developing a web app that can have hundreds of simultaneous users, or one where the UI server has to connect to a remote db server.
19 Dec, 2011, quixadhal wrote in the 5th comment:
Votes: 0
Usually, it's cheaper to keep the connection open. It varies from database to database, but there's usually a fair amount of setup and negotation involved in making a connection. Just be sure you always commit/rollback your transactions, as you want the connection to be IDLE when not in use, not IDLE IN TRANSACTION, which may prevent flushing the transaction logs to the actual table/row files.

A bug I remember from the perl DBI/DBD system with PostgreSQL used to do an implicit "begin" when you established the connection, which put you in that less desirable state, unless you knew it was doing that and did your own explicit "commit" right away.
19 Dec, 2011, Rarva.Riendf wrote in the 6th comment:
Votes: 0
Leave connection open, (the identification/driver negociation etc is quit a consuming process) and use the usual exception catch to put the rollback in it if any problem occur.
Check the connection is closed properly in case of a hard crash as well before opening a new one with the same user.
And for time out, unless you have a 'dead' world where nothing movess unless a player is on, I doubt you will have one. Should check the database parameters for it though.
19 Dec, 2011, Omega wrote in the 7th comment:
Votes: 0
Awesome everyone, thanks for the info; this is exactly the stuff I wanted to hear :) Guess I'll keep it open!
19 Dec, 2011, Runter wrote in the 8th comment:
Votes: 0
Both are fine for your purposes, probably. Test and see.
20 Dec, 2011, Omega wrote in the 9th comment:
Votes: 0
Runter said:
Both are fine for your purposes, probably. Test and see.


I've tried closing each time after a query (or series of queries in some cases) and I've also tried leaving it open. The overall usage thus-far is too small to really notice (I haven't noticed at all) I guess I will be able to give more information on this once the tables start getting to a much larger scale. But I think your right, I think it will remain very little overhead either way.
0.0/9