[R-sig-DB] Very slow MySQL access using RJDBC
Navdeep Singh
n@vdeep@@|ngh @end|ng |rom oct@ve-|m@com
Tue Jun 15 15:42:42 CEST 2010
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
More information about the R-sig-DB
mailing list