I have some code that is executing queries and storing the result in two different result sets, however having tested one of these queriess out it returns over 11 million results, so I have two result sets with over 22 million items in there and then I run out of memory, there is obviously a much better way to do this, so if anyone could give me some suggestions that would be much appreciated, below is the code I'm using and the error message I recieve:
public void getData(int tableNumber) throws SQLException
{
for (int columnNumber = 0; columnNumber < i; columnNumber++)
{
String getDataQuery = ("select " + columnNames.get(columnNumber) + " from " + tableNames.get(tableNumber));
System.out.println(getDataQuery);
stmt = connectionToActive.createStatement();
activeData = stmt.executeQuery(getDataQuery);
stmt = connectionToPassive.createStatement();
passiveData = stmt.executeQuery(getDataQuery);
activeData.close();
passiveData.close();
//TODO now use this query to extract data that I want to compare with compare class
}
}
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:80,185,216. JVM total memory size:66,650,112.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at com.pcmsdatafit.cachechecker.cc1.RetrieveCache.getData(RetrieveCache.java:95)
at com.pcmsdatafit.cachechecker.cc1.ProcessCache.execute(ProcessCache.java:35)
at com.pcmsdatafit.cachechecker.cc1.ProcessCache.main(ProcessCache.java:22)
Also is the suggestion the error message makes about "server side cursors" a good idea? I've no idea what they are.