When to open MySQL connection?

When is it better to open MySQL connection?
Is better to leave it open throughout the life of the application, or session?
Is better to open when the application or the session starting, and close when the application or session finish?
Or it is better to open the connection only when you have to use it. Example when push in a button and then close it again immediately…

I tried in different ways:

  • Open when Session start and close when finish. In this way the operations are faster because every time I save the time it takes to connect to MySQL. A new connection is created for each new session. It means a connection for each visit. But does leaving the connection open for each visit take up a lot of resources?

  • Open when Application start and close when finish. Fast as during the test in Session. A new connection is created one time at start. It means only one unique connection for all visit. But does leaving the connection open throughout the Application life take up a lot of resources?

  • Open and close the connection whenever I need it, for example by pushing a button. Very slow, every time I have to wait for the connection to MySQL. Surely it is the method that makes me consume less resources, but it is the slowest one.

What do you think?

Thanks!

I always open at application start and close on exit. It does not take up a lot of resources. The only thing to be aware of is that MySQL will time out after 8? hours. I have a mechanism that will attempt to reconnect on error and retry the sql command.

Ciao Gabriele.
That’s good question.

A session connection is faster, but for most time it will do nothing (wasted resource) and you can have a limit on the number of connections open at the same time (and so maybe this can limit the number of concurrent users)

An app connection is fast as the session one, but concurrent users could wait each one request (it’s a problem with requests that take a long time) but idle time is limited (still can exists)

An operation connection is slower, defend you on concurrent operations but, since opening a connection has a cost, is not good solution.

On not xojo web app you can see often the operation connection, but this is slow and often you can have a lot of open connection if you “forget” to close them at the end of the operation.
On bigger web app you have a “static” connection, IE it’s created for the first operation and subsequent request don’t create really a new connection but reuse this (think, for example, at the global db reference in wordpress); but at the same time is the solution nearest to a session connection.

So the solution is a session connection, created when you need (probably at session start, but not necessarily)
but keep in mind that a session can go in timeout (or better you should close it if it does nothing for a long time) so you should check if the session connection exists and is active, and eventually recreate.
In this way you can reduce the wasted connection but still have a quick response.

also if you want to use sql transactions, each user may have one transaction,
so a session for opening the database connexion is the best for me.

Every project I use a DB in I subclass the database and override the SqlSelect and SqlExecute events to call the Connect method. That makes the connect() event lazy to right before you need it and you never need to remember to call connect in advance and if your connection closes or times out it will re-connect the second you try to query something again.

Some db’s are very slow to connect and so shoving that into every select / execute can hamper performance

[quote=430474:@Norman Palardy]Some db’s are very slow to connect and so shoving that into every select / execute can hamper performance

[/quote]
If you call connect and it is already connected, doesn’t it just respond that?

its the “I’m not connected and have to reconnect” that can be slow

So then why would putting it into the select/execute really hurt performance?

Either it’s already connected and it isn’t expensive, or it isn’t connected and yeah it might be slow to connect but you need to make that connection anyways, otherwise you’d just get an exception or have to remember to call connect somewhere earlier in your code - taking up the same amount of time and resources. Calling it early seems like a worse case than it lazily right before.