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]]

