[Rd] Excessive query time running R-RODBC against SQL Server

From: Logickle <logickledoug_at_yahoo.com>
Date: Thu, 16 Oct 2008 20:22:51 -0700 (PDT)

Hi, all. I'm using RODBC to query a SQL Server 2005 database, and am experiencing terribly slow performance. I assume I'm doing something wrong, but I'm not doing anything fancy, so I'm stumped.

My setup:

Core 2 Duo 2GHz, Win XP, 2GB RAM
R 2.7.1
RODBC (latest from CRAN, not sure the version - how to tell?) SQL Server 2005
MS Access 2003
SQL Native Client ODBC driver (DSN = "HIM") SQL Server ODBC driver (DSN = "HIMs")
MS Access ODBC driver (DSN = "HIMa")

Table (T304) being queried has ~290K rows, of which the query being run should return ~78K.
Table being queried has 142 columns, of which all but 5 are type float. The other 5 are nvarchar(255)
No indices or keys - one flat table

R code looks like this:

him <- odbcConnect("HIM", "CDCS", "")
FAC <- sqlQuery(him, "Select * from T304 where f133=1")

If the DSN used points to SQL Server, the query seems to hang, though I could see in Task Manager that the R process grows, and uses most of one CPU. I killed R after 5 minutes of waiting. (If I use sqlFetch() instead and specify max records as 1000, this does return but only after 1-2 minutes.)

In SQL Server itself, the same query completes after ~6 seconds.

The same data resides in an Access database, from which it was imported into SQL Server 2005 in fact. If I use the Access DSN in my R code above, the query takes 12-15s.

How could a query against SQL Server be *so* much slower than against Access, or than SQL Server by itself?



View this message in context: http://www.nabble.com/Excessive-query-time-running-R-RODBC-against-SQL-Server-tp20026479p20026479.html
Sent from the R devel mailing list archive at Nabble.com.

R-devel_at_r-project.org mailing list
Received on Fri 17 Oct 2008 - 06:38:49 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Fri 17 Oct 2008 - 09:30:22 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-devel. Please read the posting guide before posting to the list.

list of date sections of archive