Cordova-sqlite-storage: Memory issue for adding large number of records.

[sonalk]

hi,
i m aiming to use this plugin to store around 40 tables with 40000 records in each table but my it doesnt store records more than 70000 for a single table and not more than 40000 for 2 tables. i think there is some memory issue.
i also tried increasing the memory using
SQLiteDatabase mydb = SQLiteDatabase.openOrCreateDatabase(dbfile, null); mydb.setMaximumSize(300_1024_1024);
but still the same problem.
Also i wanted to know where are these tables stored on the phone.
Hoping to get a response very soon.
Thanks,
Sonal

[sonalk]

awaiting your response

[brodybits]

FIrst, which platform do you see this on and do you see this issue in the emulator?

It would help if you can make a small test program to reproduce this issue.

Thanks,
Chris

[sonalk]

function doTest() {

test("db transaction test", function () {

    var db = window.sqlitePlugin.openDatabase({
            name : "Database",
            bgType : 1
        });

    ok(!!db, "db object");

    stop(10);

    db.transaction(function (tx) {

        start(1);
        ok(!!tx, "tx object");

        tx.executeSql('DROP TABLE IF EXISTS test_table');
        tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
        tx.executeSql('DROP TABLE IF EXISTS db');
        tx.executeSql('CREATE TABLE IF NOT EXISTS db (idd integer primary key, dataa text, data_numm integer)');
        tx.executeSql('DROP TABLE IF EXISTS abc');
        tx.executeSql('CREATE TABLE IF NOT EXISTS abc (iddd integer primary key, dataaa text, data_nummm integer)');
        tx.executeSql('DROP TABLE IF EXISTS abcd');

        for (var k = 0; k < 80000; k++) { //loop to add 80000 records


            tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function (tx, res) {}, function (e) {});
            tx.executeSql("INSERT INTO db (dataa, data_numm) VALUES (?,?)", ["abc", 100], function (tx, res) {}, function (e) {

                alert(e.message);
            });

            tx.executeSql("INSERT INTO abc (dataaa, data_nummm) VALUES (?,?)", ["abc", 100], function (tx, res) {}, function (e) {
                alert("ERROR: " + e.message);
            });
        } ////-for loop ends


    }, //db.transaction ends
        function (e) {
        console.log("ERROR: " + e.message);
    }, function () {
        console.log("tx success cb");
        alert("Final success");
        ok(true, "tx success cb");

    });
});

} //doTest

[sonalk]

hi this is the sample test code to insert 80000 records in 3 tables...
i am using phonegap cordova 2.8.1
and i m running it on android 4.1

[brodybits]

Thanks I will try it this weekend and let you know.

Chris

On Friday, October 25, 2013, sonalk wrote:

hi this is the sample test code to insert 80000 records in 3 tables...
i am using phonegap cordova 2.8.1
and i m running it on android 4.1

—
Reply to this email directly or view it on GitHubhttps://github.com/lite4cordova/Cordova-SQLitePlugin/issues/18#issuecomment-27100607
.

Sent from my mobile

[sonalk]

Thanks will wait for your response..

[brodybits]

Thank you for posting the test program. I can now see the problem in the simulator and it will be a tough one to solve. It looks like it is running out of memory within the Javascript part. I have also recently seen a similar problem in a JSON decoder.

I cannot promise when I can find a solution. Is there any chance you can try splitting up the population into multiple transaction calls?

[sonalk]

yes sure i ll split the transaction and update you with the result

[brodybits]

Limitation, needs to be investigated further and documented in readme. Batch SQL API would really help!

[brodybits]

This limitation is now documented in README.md.

[scottjpearson]

Is there any way that you could specify in more detail what you see to be the problem in the JavaScript? We have a large international project hanging on this. I'm not asking you to fix it. I'd like to see if I can fix it for you. I've got the go-ahead to devote some time to it.

[brodybits]

Is there any way that you could specify in more detail what you see to be
the problem in the JavaScript?

If you try to run the test program on Android, the Java code will crash
with an exception that memory cannot be allocated. I looked at this 1-2
years ago. The exception stack trace will show you that you run out of
memory while decoding JSON.

[scottjpearson]

Thanks.

Scott J. Pearson
REDCap Core Programmer, Project REDCap
REDCap Mobile App
http://projectredcap.org
Vanderbilt Institute for Clinical and Translational Research (VICTR)
615-322-6039 (o)

From: Chris Brody <[email protected][e-mail]>
Reply-To: litehelpers/Cordova-sqlite-storage <[email protected][e-mail]>
Date: Thursday, June 18, 2015 at 5:36 PM
To: litehelpers/Cordova-sqlite-storage <[email protected][e-mail]>
Cc: A Pearson [email protected][e-mail]>
Subject: Re: [Cordova-sqlite-storage] Memory issue for adding large number of records. (#18)

Is there any way that you could specify in more detail what you see to be
the problem in the JavaScript?

If you try to run the test program on Android, the Java code will crash
with an exception that memory cannot be allocated. I looked at this 1-2
years ago. The exception stack trace will show you that you run out of
memory while decoding JSON.

—
Reply to this email directly or view it on GitHubhttps://github.com/litehelpers/Cordova-sqlite-storage/issues/18#issuecomment-113306530.

[brodybits]

Reopening as a bug, hoping to find a solution. Will add an automatic test case for this.

UPDATE: This is caused by the JSON framework that is used in the Cordova project itself. I will make an isolated test scenario for Cordova (without this plugin) and raise a bug report when I get a chance.

[reconka]

Of course its super slow the insertion, if you are inserting 2 values in one executeSQL,
My application also working with huge dataset.. Here is my test:
tx.executeSql 1 insert 84k row 6.4sec
tx.executeSql 10 insert (bulk) 84k row 1.6 sec
tx.executeSql 100 insert (bulk) 84k row 1.3 sec

I also noticed the select can be really slow if I am using Join in my query. My workaround was simple (and silly ), I created a table, and inserted the result of the join, so I don't have to use multiple joins.

[reconka]

Here is my simple example https://gist.github.com/reconka/370b8aeb21215dbbd712 , please paste into chrome/safari web developer console and change the limitation variable
1 row/executeSQL : 35ms
100 row/executeSQL: 18ms

[brodybits]

@rekona your problem has nothing to do with this issue. This issue is specifically about a crash that happens on the Android version _only_, which happens when the Java side of the Cordova framework attempts to decode the JSON message before calling the execute() method on this plugin. Your issue _may_ be related to #288 but I cannot say for sure. I recommend that you raise a new issue, and also specify which in platform(s) you see the problem.

[brodybits]

I have added a test based on the reproduction test program by @sonalk to the cordova-sqlite-common branch, and have made a partial test fix here: https://github.com/brodybits/Cordova-sqlite-storage-dev/tree/cb-android-json-fix-1

It fixes the JSON problem in the Javascript --> Android Java direction by sending the data as a flat array instead of an array of Javascript/JSON objects. I was able to pass the rest of the test suite that is in the cordova-sqlite-common branch. However, when running the reproduction test program, it now runs into the memory issue when assembling the JSON response data. I will try to solve this part in the next few days or so.

[brodybits]

I just submitted https://issues.apache.org/jira/browse/CB-9353 to report this issue on the Apache Cordova project.

[brodybits]

Solution is now given in https://github.com/litehelpers/Cordova-sqlite-enterprise-free under GPL or purchased commercial license options.

[Samaludheen]

Hi Brodybits,
If I insert large data (appx. 40 MB base64 string) will it cause any problem?

[ravi04bit]

Hi Brodybits,

Is there a trail version for the 'Cordova-sqlite-enterprise-free ' plugin to try out if it really fixes my out of memory issue and then purchase the licensed version?

Thanks,
Ravi Kumar M