title: R: Painfully slow read performance using RODBC & SQL Server
body: <p>I am new to R but am interested in using Shiny to create dynamic charts using data stored in a SQL Server database.  To enable interactivity, I want to bring in the raw data from the database and perform calculations within <code>R</code> rather than have the database summarize the data.</p>
  
  <p>I am able to connect to the database using <code>RODBC</code>, execute a query, and receive results in a <code>data.frame</code>.  However, the read time in <code>R</code> is about 12x longer than than the same query executed in SQL Server Management Studio (SSMS).  SSMS takes ~600 ms, whereas <code>R</code> takes about 7.6 seconds.  My question is whether I am doing something wrong, or is <code>R</code> just really slow with database access?  And if so, are there faster alternatives (e.g. writing the database output to  a file and reading the file)?</p>
  
  <p>Some information about the query that may help:  The query retrieves about 250K rows with 4 columns.  The first column is a date and the other three are numeric values.  The machine running <code>R</code> and SSMS is a high-end Win 7 workstation with 32GB of memory.  The <code>R</code> command that I am running is:</p>
  
  <pre><code>system.time(df &lt;- sqlQuery(cn, query))
  </code></pre>
  
  <p>which returns:</p>
  
  <pre><code>user  system elapsed
  7.17   0.01   7.58
  </code></pre>
  
  <p>Interestingly, it appears that the data transfer from SQL to my machine is fast, but that <code>R</code> is busy doing things internally for several seconds before returning  the <code>data.frame</code>.  I see this because network utilization spikes in the first second and almost immediately returns to near 0.  Then several seconds later, the <code>R</code> <code>data.frame</code> returns.</p>