8 Replies Latest reply on Feb 10, 2012 2:42 AM by Njål Stabell

    Weak performance adding a lot of data to the SqlDatabase

    Bart Fukkink

      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

        • Re: Weak performance adding a lot of data to the SqlDatabase
          Robert Galvin

          Bart

           

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

           

          Thanks

          Rob Galvin

            • Re: Weak performance adding a lot of data to the SqlDatabase
              Bart Fukkink

              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.

            • Re: Weak performance adding a lot of data to the SqlDatabase
              Robert Galvin

              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

              1 of 1 people found this helpful
              • Re: Weak performance adding a lot of data to the SqlDatabase
                Njål Stabell

                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,[]);   

                  });

                }