Leave the database connection open for the session, or connect to it when useful
I am working in R with a mySQL database that I am communicating with RMySQL (and DBI).
I have a function that reads a SQL table (and imports them as R objects) on the fly when the user needs it. It reads the table frequently or doesn't query the database for a long time, I don't know.
Only SQL operation is allowed - read the table (SELECT *, no conditions).
Actually I open a new connection every time I need to read a new table and then close the connection. But I am looking for some tips and tricks.
I know for sure that I create the communication function as a "singleton" so as not to open two connections to the same database in parrallel.
Is it wrong to resubmit connection requests to the database? (Even if the previous connections are closed) Can I let the SQL connection open for a long time even if there is no interaction?
Thanks for any advice.
source to share
In 98% of cases, you shouldn't have to manage your db connections manually and should just leave it in ActiveRecord to connect as needed. The RMySQL adapter may already be optimized to handle most cases. Be careful not to pre-optimize, and just resort to persistent connection on the strictly needed basis.
source to share