Ucanaccess connection of my Android app with a huge MS Access database is taking too much heap space on Android device. Any alternatives?

I am developing an Android app that needs to fetch data from a huge MS Access database of 120MB size.

I have written code to establish connectivity and execute a simple query on the database. I run the same java code on my laptop and my Android device.Here's the code: p

ackage practiceDB;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;


import java.util.Scanner;

import net.ucanaccess.converters.TypesMap.AccessType;
import net.ucanaccess.ext.FunctionType;
import net.ucanaccess.jdbc.UcanaccessConnection;
import net.ucanaccess.jdbc.UcanaccessDriver;

public class Example {
    private Connection ucaConn;
    public Example() {
        try {
            this.ucaConn = getUcanaccessConnection("VehicleDatabase2.mdb");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch(IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        System.out.println("Please enter an int");
        new Scanner(System.in).nextInt();

        try {
            Example example = new Example();

            example.executeQuery();
        } catch (Exception ex) {
            System.out.println("An exception : " + ex.getMessage());
        }
    }

    private void executeQuery() throws SQLException {
        Statement st = null;
        try {
            System.out.println("Please enter an int");
            new Scanner(System.in).nextInt();
            st = this.ucaConn.createStatement();
            System.out.println("Please enter an int");
            new Scanner(System.in).nextInt();
            ResultSet rs = st.executeQuery("Select * from PersonData where EngNo = '1544256'");
            System.out.println(" result:");
            dump (rs, "executeQuery");
        } catch(Exception ex) {
            System.out.println("Sarah exception: " + ex.getMessage());
        } finally {
            if ( st != null ) {
                st.close();
            }
        }
    }

    private Connection getUcanaccessConnection(String pathNewDB) throws SQLException, IOException {
        String url  = UcanaccessDriver.URL_PREFIX + "VehicleDatabase2.mdb;newDatabaseVersion=V2003";

        return DriverManager.getConnection(url);
    }

    private void dump(ResultSet rs, String exName) 
            throws SQLException {
        System.out.println("-------------------------------------------------");
        System.out.println();

        System.out.println();
        int jk = 0;
        while (rs.next()) {

            System.out.print("| ");
            int j=rs.getMetaData().getColumnCount();
            for (int i = 1; i <=j ; ++i) {
                Object o = rs.getObject(i);
                System.out.print(o + " | ");
            }
        System.out.println();
        System.out.println();
        }
    }
}

When it runs on my laptop, the connection takes only about a minute to establish. But when it runs on my Android device, the connection takes more than 10 minutes, and takes up all the heap space, and when the device runs out of memory, the app crashes

What should i do??

Note:
i made some slight changes in this code to run it on android, like adding toasts instead of System.out.println for debugging, i removed the static main function for android, used Environment.getAbsolutePath() to locate the database, etc. Also, the code that I am running on Android, I first used a 9MB database to check if it works. The code fetches the data as expected from the 9MB database without any issues. The connection takes around 10 seconds to establish in Android in case of the 9MB database (in desktop, it takes less than a second to establish connection with 9MB database)

Solution

Yes, I know, it should work on a medium sized db. With a huge one...

Firstly, notice that the time you're measuring, it's the time of the very first connection to the database in the VM life, the followings(if needed) will be instantaneous.

Never tried something like that on Android because your experiment is challenging, yet, should it fit your requirements, you may try:

-use MirrorFolder (or keepMirror) connection parameter(see the ucanaccess web site for more details about it). In this case the very first connection to the db will be very slow, all the followings(even if the vm ends) will be instantaneous. But the access database should be updated only with ucanaccess and on your android

or, alternatively

-use a filter database(configure it on windows) that links the real database within the subset of external linked tables which are closely needed for your app(the memory usage might be dropped down). In this case, you'll have to use the remap connection parameter, because you're on a linux based SO.

See another suggestion related to jackcess(the underlying I/O library) here and use the latest ucanaccess release.