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

[Logickle]

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
No UPDATEs, INSERTs, just SELECT

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?

Thanks,

Doug 


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