SQL vs R
So, some feedback. Have installed MariaDB 10.0.10 on the Linux box. That speeded things up. Changed from InnoDB/XtraDb to Aria. That speeded loading of the data up. Have installed MariaDB on the iMac. That speeded things up more. Tried to tune MariadDB's config. Didn't speed things up much, but for the query buffer. Figured out Replication (from the linux box to the iMac). This slowed loading down somewhat. Played with the SQL. Speeded things up significantly. Played with the inxdexes. Did not speed things up much. Found what I could do in data.table that was faster than SQL and did that. Obvious increase in speed. My R processing time came down from 35 to 6 1/2 minutes. Removed all large tables before saving (and once the raw data was no longer required). That reduced RData from 150MB to 7KB. Pushed the table and image generation into a second R file. This takes 4 seconds. The corresponding LyX/LaTeX/Beamer/KnitR runs in 12 seconds. Installed RStudio. Nice. Adding new SQL queries adds between 30 and 90 seconds in the input R file, next to nothing to the presentation generation. I could not care lass how long the input takes, even hours, as long as I can save the analysis results and not the data into the RData. el PS: Ordered a MacPro :-)-O. Will report back. on 2014-05-06, 15:40 Peter Crowther said the following:
The dataset is not large by database standards. Even in mySQL - not known for its speed at multi-row querying - the queries you describe should complete within a few seconds on even moderately recent hardware if your indexes are reasonable. What are your performance criteria for processing these queries, and how have you / your team optimised the relational database storage? Cheers, - Peter -- Peter Crowther, Director, Melandra Limited On 6 May 2014 15:32, Dr Eberhard Lisse <el at lisse.na> wrote: Exactly, which is why I am looking for something faster :-)-O el on 2014-05-06, 15:21 David R Forrest said the following:
It sounds as if your underlying MySQL database is too slow for your purposes. Whatever you layer on top of it will be constrained by the underlying database. To speed up the process significantly, you may need to do work on the database backend part of the process. Dave
______________________________________________ R-help at r-project.org 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.
[[alternative HTML version deleted]]