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

[Bagatti Davide]

Hello,
thanks for your help.
 I tried with index in database (primary keys and index) but nothing
changed.
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
IDAgentPartner!=0",sep=""));

Thanks

Davide

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