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.
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.
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') };
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' });
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);
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);
var runTestButton = Ti.UI.createButton({ title:'Start Performance Test', height:50, right:5, left:5, top: 40 }); win.add(runTestButton);
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.
runTestButton.addEventListener('click',function(e){
noTransactions
variable, as the following snippet demonstrates:var noTransactions = performanceTest.run(false); noTransactionLabel.text = "No Transactions: " + noTransactions + ' ms';
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'; });
This recipe uses the helper objects discussed in this section, to perform the benchmarking operations.
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 = {
maxIterations
property controls the number of iterations in our test:maxIterations : 100001,
createOrResetDb
method is used to return a known and consistent database to test against:createOrResetDb : function(){ return Ti.Database.open("perf_test"); },
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); },
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; },
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; } };
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){
dbTestHelper
method, discussed earlier:var db = dbTestHelpers.createOrResetDb(); dbTestHelpers.resetTestTable(db);
insert
statement and timer
objects, as demonstrated here:var dummyObject = null; var insertSQL = dbTestHelpers.createSQLStatement(); var insertTimer = new my.timer("Insert Timer");
useTransaction
flag has been set, we then explicitly begin a transaction:if(useTransaction){ db.execute('BEGIN;'), }
for (var iLoop = 0; iLoop < dbTestHelpers.maxIterations; iLoop++){ dummyObject = dbTestHelpers.createDummyObject(iLoop); db.execute(insertSQL,dummyObject.id,dummyObject.name,dummyObject.date); } }
useTransaction
flag has been set, we then explicitly "commit" the transaction:if(useTransaction){ db.execute('COMMIT;'), }
var totalInsertTime = insertTimer.getResults().msElapsed; db.close(); //Return total ms elapsed return totalInsertTime; } };
3.137.178.9