[R] Performance tuning tips when working with wide datasets

[Andreas Borg]

Richard Vlasimsky schrieb:
> Does anyone have any performance tuning tips when working with datasets that are extremely wide (e.g. 20,000 columns)?
>
> In particular, I am trying to perform a merge like below:
>
> merged_data <- merge(data1, data2, by.x=ate",by.y="date",all=TRUE,sort=TRUE);
>
> This statement takes about 8 hours to execute on a pretty fast machine.  The dataset data1 contains daily data going back to 1950 (20,000 rows) and has 25 columns.  The dataset data2 contains annual data (only 60 observations), however there are lots of columns (20,000 of them).  
>
> I have to do a lot of these kinds of merges so need to figure out a way to speed it up.  
>
> I have tried  a number of different things to speed things up to no avail.  I've noticed that rbinds execute much faster using matrices than dataframes.  However the performance improvement when using matrices (vs. data frames) on merges were negligible (8 hours down to 7).  I tried casting my merge field (date) into various different data types (character, factor, date).  This didn't seem to have any effect. I tried the hash package, however, merge couldn't coerce the class into a data.frame.  I've tried various ways to parellelize computation in the past, and found that to be problematic for a variety of reasons (runaway forked processes, doesn't run in a GUI environment, doesn't run on Macs, etc.).
>
> I'm starting to run out of ideas, anyone?  Merging a 60 row dataset shouldn't take that long.
>
> Thanks,
> Richard
>
>   

Hi Richard,

I had similar problems (even with much less data) and found out that 
most of the running time was caused by memory swapping instead of CPU 
usage. If you do not need all of the merged data at once, block-wise 
processing can help, which means that you only generate that much merged 
data at once as fits into main memory. I ended up using package RSQLite 
(an embedded database) in the following way:

-create a database connection (explained in the package docs)
-copy data to database tables via dbWriteTable()
-create indices on the columns which are used for merging, sth. like: 
"dbGetQuery(con, 'create index index_year on table2(year)')" -> this 
speeds up    joining significantly
-construct an SQL query to do the join / merge operation and send it to 
SQLite via dbSendQuery()
-retreive the result in blocks of reasonable size with fetch()

Unless there is an operation in the query which requires SQLite to 
process the whole result (e.g. sorting), the result rows will be created 
on the fly for every call of fetch() instead of a huge table being 
allocated in addition to the original data.

I am not sure if this works with other database engines (there are a 
couple of database interfaces on CRAN); when I tried to use RPostgreSQL, 
it created the whole result set at once, leading to the same memory 
problem. Maybe that behavior can be changed by some config variable.

Best regards,

Andreas

-- 
Andreas Borg
Medizinische Informatik

UNIVERSITÄTSMEDIZIN
der Johannes Gutenberg-Universität
Institut für Medizinische Biometrie, Epidemiologie und Informatik
Obere Zahlbacher Straße 69, 55131 Mainz
www.imbei.uni-mainz.de

Telefon +49 (0) 6131 175062
E-Mail: borg at imbei.uni-mainz.de

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den
Absender und löschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe
dieser Mail und der darin enthaltenen Informationen ist nicht gestattet.