Resistant to impermanence - what should I use?

Question

My site has always used persistent connections based on my understanding of them; there is no reason not to. Why close the connection when it can be reused? I have a website that accesses 7 databases in total. This is not a huge traffic site, but it is quite large. What do you take on, should I use them?

Answer 1

With persistent connections:

• Effective transaction processing is not possible
• impossible user sessions on the same connection
• not scaled. Over time, you may need to extend it, and for this you will need to manage / monitor persistent connections.
• if the script for some reason cannot release the lock in the table, then any subsequent scripts will be blocked indefinitely, and you need to restart the db server. Using transactions, the transaction block will also go to the next script (using the same connection) if the script completes before the transaction block is completed, etc.

Persistent joins do not bring anything that can be done with intermittent joins.
Then why use them? The only possible reason is performance to use when the overhead of linking to your SQL Server is high. And it depends on many factors, such as:

• type of database
• will there be a MySQl server on one computer, and if not, how much? maybe outside your local network / domain?
• how much is overloaded with other processes the machine on which MySQL is sitting

You can always replace persistent connections with inconsistent connections. This may change the performance of the script, but not its behavior!

Commercial RDMS can be licensed by the number of concurrent open connections, and here persistent connections may skip

Answer 2

My knowledge in the field is limited, so I can not give you a lot of details on this issue, but as far as I know, the process of creating connections and transferring them to the stream is really worth the resources, so I would avoid it if I were you. In any case, I think that most of these solutions cannot be generalized and depend on the business.

If, for example, your application constantly interacts with the database and stops only when the application is closed, then possibly constant connections are the way to go because you avoid the process mentioned above.

However, if your application only occasionally contacts the database in order to get a little information, then closing the connection may be more reasonable, since you will not spend resources on open connections that are not used.

There is also a method called the Connection Pool, in which you create a series of a priori connections and save them there for use by other applications. In this case, the connections are stored in the database, but are not application resistant.

Note. Connections in MSSQL are always persistent for the database, since the connection pool is the default behavior.