[R] my RMySQL connections are so SLOW

[Laurent Faisnel]

Thank you for your fast answer. I work on a 450MHz Pentium II with 192 
MBytes RAM.
dbListTables() takes about 3 seconds. For direct use it's OK, but when 
requests become included in programs, it makes very slow programs !
 I had to calculate an average score for each value of a column (i=1:12) 
to fill an array, and so the operation (pretty simple) took more than 30 
seconds (almost 3 seconds per "select avg(score)..." request).

I don't think my dataset is huge enough to make it worth using ODBC, 
which would cause other problems. I do not start R for each query. I 
have the same slowness when sending the requests to R directly (without 
using source code).

What makes me hope for a solution is when you say "For me running a 
query is instant". What could be the difference between our systems ? 
The CPU speed does not explain all, in my opinion.

Laurent


/
 > version
         _
platform i686-pc-linux-gnu
arch     i686
os       linux-gnu
system   i686, linux-gnu
status
major    1
minor    6.2
year     2003
month    01
day      10
language R/


Prof Brian Ripley wrote:

>What exactly are you timing?  For me running a query is instant.
>Are you starting R for each query, for example?  What OS and what hardware 
>are you using?
>
>People have reported that RODBC is a lot faster than RMySQL for some tasks 
>involving large datasets.
>
>On Mon, 5 May 2003, Laurent Faisnel wrote:
>
>  
>
>>I'm using the latest RMySQL to send requests to a MySQL database (which 
>>is quite small for the time - let's say 20 tables with a total of 1000 
>>rows), and this should be part of a whole decisionnal system. It 
>>produces results which are displayed on a website, and therefore I need 
>>fast results (the database being quite small, I think this must be 
>>possible !).
>>Unfortunately, a simple request to the database, and R needs more than 
>>10 seconds. Even dbListTables() takes a long while. Imagine the problem 
>>with more data ! Can I hope something better  with RMySQL ? Did anyone 
>>have the same problem ?
>>If RMySQL (or is it MySQL's fault ?) is so slow, I could replace MySQL 
>>with Postgres, but this would imply a lot of extra work. R is perhaps 
>>very slow anyway ? Any piece of advice ?
>>    
>>
>
>  
>