[R] RMySQL connection very slow compared with PHP

[Laurent Faisnel]

Prof Brian Ripley wrote:

>On Mon, 5 May 2003, Laurent Faisnel wrote:
>
>  
>
>>Thank you for your fast answer. I work on a 450MHz Pentium II with 192 
>>MBytes RAM.
>>    
>>
>
>And Linux.  That's a rather old spec --  4-5 years?
>
>  
>
>>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.
>>    
>>
>
>Well, I am using a dual Athlon 2600, 1Gb RAM and a fast local disc system,
>so that is a considerable difference.  Maybe you need more RAM.
>
>  
>
OK. Clearly, I'm disadvantaged by my slow machine ! But there is another 
element I have to give you to help you understand my scepticism. R is 
called by PHP (which manages the web pages that  display amongst others 
statistical results). The first version of our system did not use R. 
Indeed PHP can access the database, and it did the work fine (and fast ! ).
Now I want to use R for a better analysis (in a in evolutive view), but 
I'm very disappointed to note how slow requests are treated. The problem 
cannot be at MySQL's level since it was so fast with PHP (about 100x 
faster I think). So I think there must be a problem with R/RMySQL. 
However, isn't there fast C code in RMySQL ?

Perhaps I do not use the best commands for the requests :

/# beginning of R program (once only)
library(DBI);
library(RMySQL);
con <- dbConnect("MySQL");

# typical request
dbGetQuery(con,"select avg(mark) from table1 where critere=1");
# this takes 2 sec. (instant with PHP & MySQL)
# table1 being a table without index, with 3 columns and only 500 rows/

By the way, how should I close ResultSets to clean the workspace (I 
tried dbSendQuery instead of dbGetQuery) ? Should I give an object name 
to dbSendQuery's result (res) and then delete it (how ? seems to be a 
problem with dbClearResult) ?

Thanks again,
Laurent