3

I'm working in R with a MySQL database to which I connect with RMySQL (and DBI).

I have a function which read SQL table (and import them as R objects) on the fly, when the user need them. He can read table often or don't send a query to the database for a long time, I can't know.

The only SQL operation allowed is reading a table (SELECT *, no conditions).

Actually I open a new connection each time I need to read a new table and then close the connection. But I'm looking for some advice and best practices.

I precise that I create a connection function as a "singleton", to avoid opening in parallel two connections to the same database.

Is it bad to repetitively send connection requests to a database ? (Even if previous connections are closed) Can I leave a SQL connection open for a long time, even if there is no interactions ?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Julien Navarre
  • 7,653
  • 3
  • 42
  • 69

1 Answers1

1

In 98% of cases, you shouldn't be managing your db connections manually and should just leave it to ActiveRecord to connect as required. RMySQL adapter has probably already been optimized to handle most cases. Be careful not to pre-optimize and just resort to persistent connection on a strictly need basis

ilan berci
  • 3,883
  • 1
  • 16
  • 21