4 Replies Latest reply on Aug 21, 2012 8:56 AM by Lei Gong

    RhoElements WebSQL with jQueryMobile and large amount of data inserting

      Hi there,

       

      I need to support RhoElements devices on a website using jQueryMobile and WebSQL. The site stops respond when inserting large amount of records. I am using RhoElements 2.0.5.27 on a MC65 unit.

       

      I attached the files here. Basically what it does is, keep inserting records to WebSQL database, while updating UI with progress information. I found that the page was stuck for sure if I showed spinning message ($.mobile.showPageLoadingMsg) when updating UI. It could success if I just update DOM without showing spinning message. But in my real project, I pull data in batches with ajax calls, it always fail even if I avoid showing any message on screen.

       

      Is the anyone have similar situation but successful?

       

      [Edit]

      Further investigation, I found that it could be related to the garbage collection of its JS engine. I ran my test while opening Pocket Controller Pro to watch its memory usage. I found that all physical memory was used up (99%) when the page stopped responding. Then I ran the same test on Chrome browser and found that Chrome would release memory regularly in the process. This might be a JS engine bug related to garbage collection.

       

      Thanks,

       

      Lei

        • Re: RhoElements WebSQL with jQueryMobile and large amount of data inserting
          Njål Stabell

          In my experience the single insert of SQLLite is very memory consuming for devices like MC65/55. I would suggest trying out the batch insert workaround I posted in this thread https://developer.motorolasolutions.com/thread/1374

          Weak performance adding a lot of data to the SqlDatabase

            • Re: RhoElements WebSQL with jQueryMobile and large amount of data inserting

              Hi Njal,

               

              It's good to know there is a way to improve sqlite performance greatly with union multiple records into one insert statement. However it did not solve the memory issue. I changed my test code to use this method, the test went much faster - but with same result - it stopped respond when all memory used up.

               

              Here is my updated js file (websql.js to replace the one in my original attached):

               

              (function($, undefined){

                        "use strict";

                        var app = (function(){

                                  var $db = openDatabase('test', '1.0', 'Test Database', 250 * 1024 * 1024);

                                  var $lasterr = '';

                                  var $total = 200000;

                                  var $batch = 200;

                                  var $count = 0;

               

                                  var start = function() {

                                            showStatus('Create table...');

                                            dropTable();

                                            createTable();

                                            showStatus('Craeting table done');

                                  };

               

                                  var test = function() {

                                            var ready = true;

                                            var iv = setInterval(function(){

                                                      if ($count >= $total) {

                                                                clearInterval(iv);

                                                                showStatus('All done: ' + $total);

                                                                $.mobile.hidePageLoadingMsg();

                                                      } else {

                                                                if (ready) {

                                                                          ready = false;

                                                                          $('#testbutton').button('disable');

                                                                          showStatus('Importing records from ... ' + $count);

                                                                          importRecords(function() {

                                                                                    ready = true;

                                                                          });

                                                                }

                                                      }

                                            }, 1000);

                                  };

               

                                  var showStatus = function(msg) {

                                            $('#status').html(msg);

                          $.mobile.showPageLoadingMsg('a', msg);

               

               

                                  };

               

                                  var dropTable = function(){

                                            $db.transaction(function (tx) {

                              tx.executeSql("DROP TABLE Test",

                                                      [], undefined, function (t, e) {

                                                          $lasterr = e;

                                                      });

                          });

                                  };

               

                                  var createTable = function(){

                                            $db.transaction(function (tx) {

                              tx.executeSql("CREATE TABLE IF NOT EXISTS Test (Id INTEGER NOT NULL, IntFld INTEGER NULL, "

                                                      + "TxtFld TEXT, PRIMARY KEY (Id))",

                                                      [], undefined, function (t, e) {

                                                          $lasterr = e;

                                                      });

                          });

                                  };

               

                                  var importRecords = function(finish){

                                            var recs = [], i;

                                            var r = 'A quick brown fox jumps over the lazy dog and a quick brown fox jumps over the lazy dog '

                                                      + 'and a quick brown fox jumps over the lazy dog ';

                                            for (i = 0; i < $batch; i++) {

                                                      recs.push({ Id: $count + i, IntFld: $batch - i, TxtFld: r + i});

                                            }

                                            importTest(recs, function(){

                                                      $count += $batch;

                                                      if ($lasterr) {

                                                                showStatus('At' + $count + 'Error:' + $lasterr);

                                                      } else {

                                                                showStatus('Imported: ' + $count);

                                                      }

                                                      $('#testbutton').button('enable');

                                                      if (finish) finish();

                                            });

                                  };

               

                                  var importTest = function(recs, finish) {

                                            var i, r, para = [], sql = 'INSERT OR REPLACE INTO Test (Id, IntFld, TxtFld)';

                                            for (i = 0; i < recs.length; i++) {

                                                      r = recs[i];

                                                      if (i > 0) sql += ' UNION ALL';

                                                      sql += ' SELECT ?, ?, ?';

                                                      para.push(r.Id, r.IntFld, r.TxtFld);

                                            }

                          $db.transaction(function (tx) {

                                                      tx.executeSql(sql, para, function () {

                                                                if (finish) finish();

                                                      }, function (t, e) {

                                                                $lasterr = e;

                                                                if (finish) finish();

                                                      });

                          });

                                  };

               

                                  return {

                                            start: start,

                                            test: test

                                  };

                        })();

               

                        $('#testpage').live('pageinit', function(){

                                  app.start();

               

                                  $('#testbutton').click(function(){

                                            app.test();

                                  });

                        });

              })(jQuery);

            • Re: RhoElements WebSQL with jQueryMobile and large amount of data inserting
              Robert Galvin

              Lei

               

              I see a problem with the way the Database size is allocated versus the call to create the Database

               

              In the config.xml you specify:

                   <WebDB>
                      <WebStorageDBPath value="file://%INSTALLDIR%"/>
                      <WebSQLDBQuota value="250000000"/>
                      <WebSQLDBPath value="file://%INSTALLDIR%"/>
                    </WebDB>
              

               

              But in your JS file you specify:

              var $db = openDatabase('test', '1.0', 'Test Database', 250 * 1024 * 1024);
              

               

              So doing the math I get 262144000 bytes - so the openDatabase call is failing. I modified my config.xml to be

                    <WebDB>
                      <WebStorageDBPath value="file://%INSTALLDIR%"/>
                      <WebSQLDBQuota value="280000000"/>
                      <WebSQLDBPath value="file://%INSTALLDIR%"/>
                    </WebDB>
              

               

              I also commented out the showPageLoadingMsg call in your showStatus function. This is quite taxing on Windows mobile on the animation as well as how often it has to drawn the transparent image with animation. This is what was probably causing the memory spike - not the database methods.

              var showStatus = function(msg) {
                                            $('#status').html(msg);
                          //$.mobile.showPageLoadingMsg('a', msg);
              
              
                                  };
              

               

              I also removed the jquery mobile css file. These have many CSS3 elements that tend to cause memory problems on Windows Mobile. After all of that I was able to execute the test and the CPU utilization stayed around 75%

              One other thing to consider is that your 200,000 records will take up around 60MB, so you may want to change the location of the WebSQL db to the SD card instead of the RAM; <WebStorageDBPath value="file://%INSTALLDIR%"/>


                • Re: RhoElements WebSQL with jQueryMobile and large amount of data inserting

                  Thank you Rob. Your reply did help me a lot.

                   

                  I got reply from RE development team that there are bugs in current RE version in handling CSS (memory leak). In my real project, I use jQuery Mobile which applied many fancy HTML5/CSS3 features which caused trouble to my pages on MC65. I have to use some alternatives to avoid those features like showing spinning message. Anyway I got this part passed.

                   

                  Another thing to mention, I got another issue after this part. It got stuck random when I retrieve large amount of data via ajax and import them to WebSQL database. I struggled this for 2 days to find out the cause - it cannot handle too much data at once. I split data in small parts and handle them respectively then the issue went away. This happened on my MC65 unit, not sure if this applies to other models.