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

From: Bagatti Davide <bagatti.davide_at_gmail.com>
Date: Wed 03 Jan 2007 - 19:44:44 GMT

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@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@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 06:50:08 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.