Re: [R] loading data and executing queries with R and Mysql

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Wed 03 Jan 2007 - 14:54:55 GMT

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

______________________________________________
R-help@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.
Received on Thu Jan 04 02:04:35 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Wed 03 Jan 2007 - 20:30:35 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.