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.
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.
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.
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.
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.