Memory-efficient subsetting of large data.table

Question

I have a SQLite-db with a size of 11 GB and 16 GB of RAM (shared with OS and so on). I want to perform a subsetting method with data.table:

# database connection
con = dbConnect(dbDriver("SQLite"), dbname=sqlite_database)

# read table from database
inventory <- as.data.table(dbGetQuery( con,'select * from inventory'))

# subset table
unfulfilled_inventory <- inventory[period >= stableStateStart, .(period, articleID, unfulfilledQuantities, backlog, stock)]

Getting more RAM would be the cheapest way to solve this problem, but unfortunately this is not an option.

The inventory object has 127,500,000 rows with 6 variables. The inventory object has an allocated size in memory of 5.2 GB.

dim(unfulfilled_inventory)
[1] 127500000         6

Is there a way to do this subsetting in a more memory-efficient way? I tried building a vector for vector scanning, but it has the same result. Or is there a way to use swap space for this operation (I do not really care about speed).

Just to confirm, which object is 5.2GB here? Also what does dim(inventory) result in? — Arun
– Arun, Commented Sep 24, 2015 at 13:23

jangorecki · Accepted Answer · 2015-09-24 13:12:06Z

4

The only two I have in mind at the moment:

use setDT instead of as.data.table, you will save some memory when reading from db.
You can compute your condition on the database side as then use computed column in R:

sql = "SELECT *, period >= stableStateStart AS tmpcol FROM inventory"
inventory = setDT(dbGetQuery(conn, sql), key="tmpcol")
inventory[.(TRUE)]

Adding ORDER BY tmpcol to sql query may also help on setDT(., key="tmpcol") in later step.

Be sure to use data.table 1.9.6 - recently published to CRAN.

edited Sep 24, 2015 at 13:12

answered Sep 24, 2015 at 12:34

jangorecki

16.8k5 gold badges86 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Memory-efficient subsetting of large data.table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related