[R] loading data and executing queries with R and Mysql
bagatti.davide at gmail.com
Wed Jan 3 20:44:44 CET 2007
thanks for your help.
I tried with index in database (primary keys and index) but nothing
My hard disk has two partition FAT 32: in the first there is ubuntu 6.10, in
the second (19 GB with 6GB of free space, never defragmented) there is
Windows XP HE. I have the problem under Windows.
In attach (csv file) you find an example of data I am using: it is only one
step, so I load (with "load data") one file of this type for each step of
the simulation (these are about 1000 steps, but the problem happens when I
load more than 80-100 files ).
The queries are similar to the following:
dbGetQuery(con, paste("select ID,IDAgentPartner,Vote from
TabAgentRelationships where step= ",passo," and Vote > ",min_voto," and
2007/1/3, Prof Brian Ripley <ripley at stats.ox.ac.uk>:
> 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
> > 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"
> > 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%
> > 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.
> > 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
> > 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
More information about the R-help