I've noticed that RODBC seems to be an order of magnitude slower in
retrieving rows from a table than ODBC (or JDBC) access in other tools.
I'm wondering why that is, and whether there are things I can do to
improve performance beyond what I've tried. I've checked the
documentation, and have tried the rows_at_time= and buffsize= options,
which helped a little, but it is still running much, much slower than
what I'm seeing from other querying tools.
I'm running R version 2.7.1 on Windows XP 32-bit, RODBC version 1.2-3,
and connecting to SQL Server 2005 running on a separate server via an
ODBC DSN.
The table "mytable" has 67 columns, and about 68,000 rows, and running
a
simple "select * from mytable" query
10 seconds Excel 2007 (ODBC via Microsoft Query)
14 seconds RapidMiner (Java-based data mining application,
connecting to database via JDBC)
19 seconds Import table from ODBC source to Microsoft Access
2007
229 seconds R using: system.time(my.data <- sqlQuery(my.db,
"select * from mytable"))
161 seconds R using: system.time(my.data <- sqlQuery(my.db,
"select * from mytable", rows_at_time=1024, buffsize=70000))
Any ideas on other things to try? Thank you.
--
Keith
[[alternative HTML version deleted]]
