[R-sig-DB] Very slow MySQL access using RJDBC

[Navdeep Singh]

Hi all,

I am having severe speed issues when trying to use R (version 2.10.1, 32-bit) and RJDBC (0.1-5) to access tables on a remote MySQL (5.1.47-community) via the driver "com.mysql.jdbc.Driver" as supplied with the MySQL client on my workstation. I am using a very simple select query to select a fairly large number (hundreds of thousands) of rows from a table as follows:

Library(RJDBC);
db.loc <- "jdbc:mysql://192.168.0.2/schema";
dbDrvJDBC <- JDBC("com.mysql.jdbc.Driver");
conn <- dbConnect(dbDrvJDBC,
                  db.loc,
                  user="user",
                  password="pass");
sqlMom <- paste("select p_date, id, s_1006, s_1054",
              "from signals",
              "where s_1025 = 1",
              "and p_date>'2010-01-01'");
data.mom <- dbGetQuery(conn,sqlMom);

For example this brings back about 70,000 rows and takes a couple of minutes to run. Unfortunately the time taken seems to scale with the number of records returned, and I need to work with several datasets 10-20 times this size. Running this same query in the SQL client or in Matlab using the same JDBC driver takes a couple of seconds.

I thought the slowdown might be due to restricted Java heap size so tried increasing this (to no avail) via:

Options(java.parameters="-Xmx900M");

Also memory.limit() indicates R has 3583MB available (of 12GB physical).


I also had a brief attempt at using the RMySQL package, but this did not work, I am guessing due to having the client rather than the MySQL server installed on my machine. I made a go of moving around the required  libmySQL.dll so that RMySQL could find it, but ultimately got the following error when using dbConnect as above (with the MySQL driver instead):

RS-DBI driver: (Failed to connect to database: Error: Can't connect to MySQL server on 'localhost' (10061)


I am still missing something, having scanned through the Data Import/Export manual, previous mailing list posts and Google this morning so any guidance would be appreciated very much.


Best wishes,
Navdeep



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5198 (20100615) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email