Weak performance adding a lot of data to the SqlDatabase

Good day to you all,

We have been working on testing RhoElements to see if it can be strong enough to handle the demands we have for our application. Currently our application is specifically made for Windows Mobile, and in the near future we want to move this to a more general platform so that we can run it on multiple devices. That is what we are testing out HTML5 for in combination with RhoElements mostly for hardware coverage.

So far so good, we made our HTML5 application, got a database up and running, testing around a bit in Google Chrome on desktops. And there things worked quite good. However, when we started to run the same tests on tablets (iPad 1 and Motorola Xoom), we see a great decline in performance. Importing 1000 records into a database was undoable on the iPad (crashed Safari) and was highly unpleasant at the Motorola Xoom (long wait time and if interrupted, not all data was inserted).

Now to complete our tests, we also do this on a Motorola MC 55 running in RhoElements. I had to make some tweaks to get it up and running. But in the end I have got it working quite ok. Altho the performance is somewhere in between of the iPad and the Motorola Xoom. Sometimes the application just crashes without useable log entry's to define the problem, and other moments the wait time is so long that this is undesirable.

Now after the, quite long, introduction, my question: Would I be able to upgrade the current solution for adding data to the database so that this would increase the performance of the application?

To create the application I have used this blog entry to get started: https://motorola-dev.hosted.jivesoftware.com/community/mobile_computing/rhoelements/rhoelements_blog/blog/2011/10/01/rhoelements--building-your-first-app--part-4-using-websql

After I got this example working, I have made a couple of changes to it:

- For the purpose of the test I work with one table.

- This single table consists of 23 fields. (8 Integer fields, 11 Text fields, 2 Date fields and 2 real fields, if it matters )

Also made an input fields and a button. Enter a number in the input field and when you click the button, generate and add that number of rows in the database.

Adding 10 rows in the database goes fine, with an average about 2 seconds.

Adding 100 rows becomes quite a bit harder. Takes about 20 seconds.

Adding 500 rows becomes a strain on the system. 107 seconds.

Adding 1000 rows never made it to the end.

As you can see, this goes up quite fast, and comes to a point where it just wont respond anymore.

Could anyone give me some more insight in idea's to optimize the database performance? For example: would it be possible to execute several query's in 1 transaction? This should speed up everything greatly since it doesn't need to open and close the database for every query. Other solutions would be welcome as well of course.

Thanks for reading and hopefully someone could give me a hand in this.

Kind regards,

Bart

Robert Galvin
BartWhat version of

Bart

What version of RhoElements are you using? and can you share your test page?

Thanks

Rob Galvin

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Darryn Campbell
Rob,Currently we are using

Rob,

Currently we are using version 1.0.2.3.


And here is an example of the page we are using (simplified and anonymized).

Index.htm:

<html>

   <head>

      <title>Hello Scan</title>

            <script src="db.js" type="text/javascript"></script>

            <script src="data.js" type="text/javascript"></script>

            <script>

                    function onPadeLoad()

                    {

                              dbInit();

                    }

                    function startTest()

                    {

                              var inpAmount = document.getElementById( 'txtAmount' );

                              var aantal = inpAmount.value;

                              var articles = new Array();

                              startTimer();

                              for ( var i = 0; i < aantal; i++ ) {

                                        var art = new a( i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i );

                                        articles.push( art );

                              }

                              //Empty the db - to simulate a real application

                              Deletea( null, function ()

                              {

                                        //Save the new articles

                                        Saveas( articles, function ()

                                        {

                                                  endTimer();

                                        } );

                              } );

                    }

                    function startTimer()

                    {

                              starttime = new Date();

                              var s = document.getElementById( 'start' );

                              s.innerHTML = starttime.getHours() + ":" + starttime.getMinutes() + ":" + starttime.getSeconds() + " " + starttime.getMilliseconds();

                    }

                    function endTimer()

                    {

                              var endtime = new Date();

                              var e = document.getElementById( 'end' );

                              e.innerHTML = endtime.getHours() + ":" + endtime.getMinutes() + ":" + endtime.getSeconds() + " " + endtime.getMilliseconds();

                              var duur = endtime.getTime() - starttime.getTime();

                              var d = document.getElementById( 'dur' );

                              d.innerHTML = duur;

                    }

            </script>

   </head>

   <body onLoad="onPadeLoad();">

            <input type="text" id="txtAmount" value="100" /> <input type="button" value="click!" onClick="startTest()" />

            <div id="start"></div>

            <div id="end"></div>

            <div id="dur"></div>

   </body>

</html>

db.js:

function dbInit()

{

          db = openDatabase(

                    "db",

                    "1.0",

                    "",

                    10 * 1024 * 1024 );

          db.transaction( function (tx)

          {

                    tx.executeSql( "CREATE TABLE a( Id INTEGER PRIMARY KEY, a TEXT DEFAULT '', b INTEGER DEFAULT 0, c INTEGER DEFAULT 0, d INTEGER DEFAULT 0, e TEXT DEFAULT '', f TEXT DEFAULT '', g TEXT DEFAULT '', h REAL DEFAULT 0, i TEXT DEFAULT '', j TEXT DEFAULT '', k TEXT DEFAULT '', l TEXT DEFAULT '', m INTEGER DEFAULT 0, n INTEGER DEFAULT 0, o INTEGER DEFAULT 0, p TEXT DEFAULT '', q TEXT DEFAULT '', r TEXT DEFAULT '', s DEFAULT( DATETIME() ), t DEFAULT( DATETIME() ), u INTEGER DEFAULT 0, v REAL DEFAULT 0 )", [],

                    function(tx)

                    {

                              //do nothing on success

                    }, onError );

          });

}

function onError( tx, error )

{

          generic.Log( error.message, 1 );

}

data.js:

function a(id, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v) {

          this.Id = parseInt(id);

          if (!isFinite(this.Id)) this.Id = -1;

          if (typeof (a) != "object" && typeof (a) != "undefined") this.A = a.toString();

          else this.A = "";

          this.B = parseInt(b);

          if (!isFinite(this.B)) this.B = -1;

          this.C = parseInt(c);

          if (!isFinite(this.C)) this.C = -1;

          this.D = parseInt(d);

          if (!isFinite(this.D)) this.D = -1;

          if (typeof (e) != "object" && typeof (e) != "undefined") this.E = e.toString();

          else this.E = "";

          if (typeof (f) != "object" && typeof (f) != "undefined") this.F = f.toString();

          else this.F = "";

          if (typeof (g) != "object" && typeof (g) != "undefined") this.G = g.toString();

          else this.G = "";

          this.H = parseFloat(h);

          if (!isFinite(this.H)) this.H = 0;

          if (typeof  != "object" && typeof  != "undefined") this.I = i.toString();

          else this.I = "";

          if (typeof (j) != "object" && typeof (j) != "undefined") this.J = j.toString();

          else this.J = "";

          if (typeof (k) != "object" && typeof (k) != "undefined") this.K = k.toString();

          else this.K = "";

          if (typeof (l) != "object" && typeof (l) != "undefined") this.L = l.toString();

          else this.L = "";

          this.M = m == true;

          this.N = n == true;

          this.O = o == true;

          if (typeof (p) != "object" && typeof (p) != "undefined") this.P = p.toString();

          else this.P = "";

          if (typeof (q) != "object" && typeof (q) != "undefined") this.Q = q.toString();

          else this.Q = "";

          if (typeof (r) != "object" && typeof (r) != "undefined") this.R = r.toString();

          else this.R = "";

          if ( typeof( s ) == "string" )

          this.S = new Date( parseInt( s.substr( 6 ) ) );

          else if (s instanceof Date) this.S = s;

          else this.S = new Date();

          if ( typeof( t ) == "string" )

          this.T = new Date( parseInt( t.substr( 6 ) ) );

          else if (t instanceof Date) this.T = t;

          else this.T = new Date();

          this.U = parseInt(u);

          if (!isFinite(this.U)) this.U = -1;

          this.V = parseFloat(v);

          if (!isFinite(this.V)) this.V = 0;

}

function Savea(object, callback) {

          db = openDatabase(

                    "db",

                    "1.0",

                    "",

                    10 * 1024 * 1024 );

          db.transaction(

                    function (transaction) {

                              if (object.Id === -1) {

                                        transaction.executeSql(

                                                  "SELECT MIN(-1, IFNULL(MIN(Id), -1))-1 AS Id FROM a",

                                                  [],

                                                  function (transaction, resultSet) {

                                                            SaveaId(resultSet.rows.item(0).Id, object, transaction, callback);

                                                  }

                                        );

                              }

                              else {

                                        SaveaId(object.Id, object, transaction, callback);

                              }

                    }

          );

}

function SaveaId(id, object, transaction, callback) {

          transaction.executeSql(

                    "INSERT OR REPLACE INTO a (Id, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

                    [id, object.A, object.B, object.C, object.D, object.E, object.F, object.G, object.H, object.I, object.J, object.K, object.L, object.M, object.N, object.O, object.P, object.Q, object.R, object.S, object.T, object.U, object.V],

                    function (transaction, resultSet) { if (callback) callback(); },

                    function (transaction, error) { if (callback) callback(error); }

          );

}

function Saveas(objects, callback) {

          var done = 0;

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

                    Savea(

                              objects[i],

                              function (error) {

                                        done++;

                                        if (done == objects.length && callback) {

                                                  callback();

                                        }

                              }

                    );

          }

}

function Loada(callback, constraint, sort) {

          db = openDatabase(

                    "db",

                    "1.0",

                    "",

                    10 * 1024 * 1024 );

          db.transaction(

                    function (transaction) {

                              var parameters = GetParameters(constraint);

                              transaction.executeSql(

                                        "SELECT Id, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v FROM a" + parameters.Where + GetSort(sort),

                                        parameters.Values,

                                        function (transaction, resultSet) {

                                                  var resultArray = [];

                                                  for (var i = 0; i < resultSet.rows.length; i++) {

                                                            resultArray.push(Art_articleFromObject(resultSet.rows.item(i)));

                                                  }

                                                  callback(resultArray);

                                        },

                                        function (transaction, error) {

                                                  callback(undefined, error);

                                        }

                              );

                    }

          );

}

function Deletea(constraint, callback) {

          db = openDatabase(

                    "db",

                    "1.0",

                    "",

                    10 * 1024 * 1024 );

          db.transaction(

                    function (transaction) {

                              var parameters = GetParameters(constraint);

                              transaction.executeSql(

                                        "DELETE FROM a" + parameters.Where,

                                        parameters.Values,

                                        function (transaction, resultSet) { if (callback) callback(); },

                                        function (transaction, error) { if (callback) callback(error); }

                              );

                    }

          );

}

function GetParameters(constraint) {

          var where = "";

          var values = [];

          if (constraint) {

                    where += " WHERE ";

                    if (typeof (constraint) != "object") {

                              where += "id = ?"

                              values.push(constraint);

                    }

                    else {

                              var first = true;

                              for (var field in constraint) {

                                        if (constraint.hasOwnProperty(field)) {

                                                  if (first) first = false;

                                                  else where += " AND ";

                                                  where += field + " = ?";

                                                  values.push(constraint[field]);

                                        }

                              }

                    }

          }

          return { Where: where, Values: values };

}

function GetSort(sort) {

          if (sort) {

                    return " ORDER BY " + sort;

          }

          else {

                    return ""

          }

}

You can copy & paste these into the selected files and reproduce it. Note: You also gotta update the config file:

<WebSQLDBQuota value="10500000"/>

This is because we expect the database to be around 10MB at most.

Hope this helps.

Kind Regards.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Njål Stabell
The main problem is that

The main problem is that SqlLite only does single inserts.. if you don't tweak the code a bit :-)

We where able to increase speed for one of our customers so that they now can store over 7000 rows in less than 10 seconds.. the trick is using union select. And after several tests I ended up with inserting 250 lines per transaction for best performance. (This may vary depending on the number of fields you have)

Here is a codesample (from after a successful json ajax call)

success: function(data) {

  var newQuery = true, query = '';

  var i = 0;

    $.each(data, function(key, val) {

   i = i + 1;

    var DESC= (val.MAKTX);

    var EANNAME = (val.EAN11);

    var ASWNUMBER = (val.BISMT);

   

   

      if (newQuery) {

        query = 'INSERT INTO names (EANNAME, ASWNUMBER, DESC)';

        newQuery = false;

    }

    else {

        query += ' UNION';

    }   

           query += ' SELECT "'+EANNAME+'", "'+ASWNUMBER+'", "'+DESC+'"';

          

       if (i!=0 && i%250==0) {

                 

                 

        insertData(query);

        newQuery = true;

    }

});

//executing remaining lines

if (i%250!=0) {

 

                         insertData(query);

                         $.mobile.hidePageLoadingMsg();

                         delete data;

}

},

    error: function (data) {

              $.mobile.hidePageLoadingMsg();

        alert("You need to be online!");

    }

});

};

function insertData (query) {

                    localDB.transaction(function(transaction){

            transaction.executeSql(query,[]);   

  });

}

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Bart Fukkink
That surely sounds very

That surely sounds very helpfull. I will give it a try and see if this can work for us.

Thank you!

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Bart Fukkink
Njal,Thank you for your

Njal,

Thank you for your example and insight in this situation. With the code you provided us, we have succesfully created a prototype wich is able to save a lot of data in a very short timespan.

Bart

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Darryn Campbell
Great!I was stuck with a non

Great!

I was stuck with a non acceptable solution myself a few months back, where I needed to load up to 10000 articles into motorola devices for offline retail functionality (limited wi-fi in the stores). This piece of code saved the entire project

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
I tested with the soon to be

I tested with the soon to be released 1.0.3.8 and do not have an issue. We did make some changes in this area, so maybe the issue has been fixed. I tested this on a ES400 and an ET1

Vote: 
Vote up!
Vote down!

Points: 1

You voted ‘up’


Bart Fukkink
Rob,The new version alone did

Rob,

The new version alone did not show any major differences. But combined with Njal's sollution we where able to recieve a massive time profit. I have only tested the sollution with the 1.0.3.8 version, but I think it will work in other versions to.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Log in to post comments