RJDBC(dbGetQuery) - GC overhead limit exceeded

Question

I'm using the package RJDBC and there the function dbGetQuery to get the ouput of a SQL query. The code works with SQL statements with not so much rows, but which statements which rows > 1.000.000 I get an error. Is there a parameter to handle the memory?

dbGetQuery(conn,"SQL..")

And then I get the following error-message:

Error in .jcall(rp, "I", "fetch", stride, block) :
java.lang.OutOfMemoryError: GC overhead limit exceeded

Thanks! R007

Answer 1

As this article mentions, putting the java parameters argument at the TOP of your code BEFORE you load your libraries should increase your heap size from the default 512 MB. If you've already loaded your packages you'll need to restart R and re-run the code for the changes to be applied.

https://www.ibm.com/support/pages/executing-code-r-connecting-impala-jdbc-rjdbc-results-error-jcallrp-i-fetch-stride-javalangoutofmemoryerror-java-heap-space-rjdbcdbgetquery-gc-overhead-limit-exceeded

# Do this first    
options(java.parameters = "-Xmx4g")

# Then load your libraries
library(XLConnect)
library(RJDBC)

Next, you can use the XLConnect package to free up Java memory as often as you need it.

# Free up java memory
XLConnect::xlcFreeMemory()

If you're running a huge loop like I am, you may want to insert this into the loop to free up memory before re-entering and running again.

Install the package before running the code of course:

# Install XLConnect
install.packages("XLConnect")

Those 2 things together may be enough to fix your issue.

Further reading on this topic is found here: "Out of Memory Error (Java)" when using R and XLConnect package