Tuning your SQLite database for maximum performance

Data access is a common bottleneck in Titanium Enterprise development. Through proper use of SQLite transactions, you can experience up to a 10x improvement in bulk insert operations.

SQLite transactions provide reliable units of work that allow for data recovery and keep the database consistent. By default, each time an insert, update, or delete operation is performed on an SQLite database, an implicit transaction is created before and after your statement is executed. This helps keep your database in a consistent state. For batch operations however, this introduces an additional level of overhead and can drastically reduce your app's performance.

This recipe demonstrates how to use SQLite transactions to improve app performance when conducting batch SQL actions and working with large datasets.

Getting ready

This recipe will run a performance comparison using 100,000 rows. Included in this recipe is the following basic UI that allows you to benchmark your different devices.

Getting ready

Adding the TimeLogger module

This recipe uses TimeLogger to record the duration of each performance test. To add the TimeLogger module to your project, copy the timelogger.js file into your project, as shown in the following screenshot:

Adding the TimeLogger module

How to do it…

The first step in this recipe is to create the application namespace and import the timer module, as the following snippet demonstrates:

//Create our application namespace
var my = {
  timer : require('timelogger')
};

Creating our testing Interface

The next step is to create a Ti.UI.Window for our recipe. This will be used to provide a launching point for our performance tests.

var win = Ti.UI.createWindow({
  backgroundColor:'#fff',layout:'vertical'
});
  1. Now, a label is created to track the results of our No Transactions performance test:
    var noTransactionLabel = Ti.UI.createLabel({
    text: "No Transactions: NA",height:20, right:5, left:5, top: 40, textAlign:'left',color:'#000', font:{fontWeight:'bold',fontSize:14}
});
win.add(noTransactionLabel);
  2. Then, a label is created to track the results of our With Transactions performance test:
    var withTransactionLabel = Ti.UI.createLabel({
  text: "With Transactions: NA",
  height:20, right:5, left:5, 
  top: 10, textAlign:'left',
  color:'#000', font:{fontWeight:'bold',fontSize:14}
});
win.add(withTransactionLabel);
  3. The final UI element we create is a button that, when pressed, will launch our tests:
    var runTestButton = Ti.UI.createButton({
    title:'Start Performance Test', height:50, right:5, left:5, top: 40
});
win.add(runTestButton);

Benchmarking

When the runTestButton button is pressed, a benchmark 100,000 record insert is performed, both with and without the use of transactional scoping. The screen is then updated with the elapsed milliseconds for each test.

  1. The following code demonstrates how each test is run when the button's click event is triggered:
    runTestButton.addEventListener('click',function(e){
  2. First, a test is run without explicitly creating a transaction. This test returns the number of milliseconds elapsed while running, and stores the result in the noTransactions variable, as the following snippet demonstrates:
    var noTransactions = performanceTest.run(false);
noTransactionLabel.text = "No Transactions: " + noTransactions + ' ms';
  3. Next, the same test is run using BEGIN and COMMIT statements to create an explicit transaction. This test returns the milliseconds elapsed during the test, and stores the result in the withTransactions variable, as shown in the following snippet:
    var withTransactions = performanceTest.run(true);
withTransactionLabel.text = "With Transactions: " + withTransactions + ' ms';
});

How it works…

This recipe uses the helper objects discussed in this section, to perform the benchmarking operations.

Database setup

dbTestHelpers is the first helper object used during benchmarking. This object contains all the code needed to set up, create, and manage the database used in our tests:

var dbTestHelpers = {
  1. The maxIterations property controls the number of iterations in our test:
      maxIterations : 100001,
  2. The createOrResetDb method is used to return a known and consistent database to test against:
      createOrResetDb : function(){
    return Ti.Database.open("perf_test");
  },
  3. The resetTestTable method is then called to drop and re-create our test table. This allows us to run our tests several times, while maintaining a consistent sample size:
      resetTestTable : function(db){
    var dropSql = 'DROP TABLE IF EXISTS TEST_INSERT';
    var createSql = 'CREATE TABLE IF NOT EXISTS ';
    createSql += 'TEST_INSERT ';
    createSql += '(TEST_ID INTEGER, TEST_NAME TEXT, ';
    createSql += 'TEST_DATE DATE)';
    db.execute(dropSql); 
    db.execute(createSql);
  },
  4. The createSQLStatement method returns the SQL that will be used to perform our insert operations:
      createSQLStatement : function(){
    var sql = 'INSERT INTO TEST_INSERT ';
    sql += '(TEST_ID, TEST_NAME, TEST_DATE) ';
    sql += 'VALUES(?,?,?)';
    return sql;
  },
  5. The createDummyObject function creates a unique object, to be inserted into each row:
      createDummyObject : function(iterator){
    var dummy = {
      id:iterator, name : 'test record ' + iterator, date : new Date()
    };

    return dummy;
  }
};

Performing the tests

The performanceTest object runs and times the recipe database's inserts. The run method starts our benchmark and provides an indicator if the test is to use transactions or not:

var performanceTest = {
  run : function(useTransaction){
  1. The first step in our test is to create a database connection and to reset our table. This is done by calling the dbTestHelper method, discussed earlier:
        var db = dbTestHelpers.createOrResetDb();
    dbTestHelpers.resetTestTable(db);
  2. After our database has been set up, the next step is to create our insert statement and timer objects, as demonstrated here:
        var dummyObject = null;
    var insertSQL = dbTestHelpers.createSQLStatement();
    var insertTimer = new my.timer("Insert Timer");
  3. If the useTransaction flag has been set, we then explicitly begin a transaction:
        if(useTransaction){
      db.execute('BEGIN;'),
    }
  4. In the next step of this recipe, a loop is created, to insert records into the test table, a specific number of times. By default, this test will insert 100,000 records, and time the total duration.
        for (var iLoop = 0; iLoop < dbTestHelpers.maxIterations; iLoop++){
      dummyObject = dbTestHelpers.createDummyObject(iLoop);
      db.execute(insertSQL,dummyObject.id,dummyObject.name,dummyObject.date);
    }
  }
  5. If the useTransaction flag has been set, we then explicitly "commit" the transaction:
      if(useTransaction){
    db.execute('COMMIT;'),
  }
  6. The final step of this method is to retrieve our execution duration from our timer object. This value (in milliseconds) is then returned for later comparison operations:
      var totalInsertTime = insertTimer.getResults().msElapsed;
  db.close();
  //Return total ms elapsed
  return totalInsertTime;
  }
};

See also

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.194.57