R help - Jan 2007 - loading data and executing queries with R and Mysql

Hello everyone,

I have a problem when I execute queries using R 2.3.1 and MySql server 5.0.
What I do: I load data in different csv files (every file represents a
particular temporal step of a simulation) using Mysql query "load
data" with
RMySQL command DbSendQuery (but the same problem there is also using
DbWritetable). Then I use a function where I have a lot of queries that
interact with the database.
Well, while loading data is very fast, query execution is very slow
...looking to Windows task manager I see that my cpu doesn't go to 100% of
usage, but only at 30-45%. Looking at the processes I see Rgui.exe use
between 0-23% and mysql-nt.exe use between 8-20% and so it is very slow. I
am sure my cpu has no particular problems.
Could you help me?

Thanks in advance

Davide

	[[alternative HTML version deleted]]

Nevermind the CPU usage, the likely problem is that your queries are
inefficient in one or more ways (i.e., you don't use indexes when you
really should - it's impossible to guess without knowing how the data
and the queries look like, which somehow you've decided are not
important enough to describe in your email). One nice reference is
http://highperformancemysql.com/

> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Bagatti Davide
> Sent: Wednesday, January 03, 2007 8:05 AM
> To: r-help at stat.math.ethz.ch
> Subject: [R] loading data and executing queries with R and Mysql
>
> Hello everyone,
>
> I have a problem when I execute queries using R 2.3.1 and
> MySql server 5.0.
> What I do: I load data in different csv files (every file represents a
> particular temporal step of a simulation) using Mysql query
> "load data" with
> RMySQL command DbSendQuery (but the same problem there is also using
> DbWritetable). Then I use a function where I have a lot of
> queries that
> interact with the database.
> Well, while loading data is very fast, query execution is very slow
> ...looking to Windows task manager I see that my cpu doesn't
> go to 100% of
> usage, but only at 30-45%. Looking at the processes I see Rgui.exe use
> between 0-23% and mysql-nt.exe use between 8-20% and so it is
> very slow. I
> am sure my cpu has no particular problems.
> Could you help me?
>
> Thanks in advance
>
> Davide
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

Without any actual example Ias requested in the footer of this message) I 
can only guess, but the most common cause of slow queries is the lack of 
indices in the database, so did you create any?

You haven't told us your actual OS (beyond 'Windows'), but a guess
is that
your processes are I/O bound, and that your file system could well do with 
a tune.  For example, if this is NTFS, is there lots (at least 30%) of 
free space and did you defragment it after saving the data?

On Wed, 3 Jan 2007, Bagatti Davide wrote:
> Hello everyone,
>
> I have a problem when I execute queries using R 2.3.1 and MySql server 5.0.
> What I do: I load data in different csv files (every file represents a
> particular temporal step of a simulation) using Mysql query "load
data" with
> RMySQL command DbSendQuery (but the same problem there is also using
> DbWritetable). Then I use a function where I have a lot of queries that
> interact with the database.
> Well, while loading data is very fast, query execution is very slow
> ...looking to Windows task manager I see that my cpu doesn't go to 100%
of
> usage, but only at 30-45%. Looking at the processes I see Rgui.exe use
> between 0-23% and mysql-nt.exe use between 8-20% and so it is very slow. I
> am sure my cpu has no particular problems.
> Could you help me?
>
> Thanks in advance
>
> Davide
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595