Storage API Insert statement freezes firefox in bootstrapped

[SuvP]

I am developing a bootstrapped add on.

Data to be inserted has just two TEXT columns whose individual length don't even exceed 256.

I initially used executeSimpleSQL since I didn't need to get any results. It worked for simulataneous inserts of upto 20K smoothly i.e. in the bakground no lag or freezing observed.

However, with 0.1 million I could see horrible freezing during insertion.

So, I tried these two,

Insert in chunks of 500 records - This didn't work well since even for 20K records it showed visible freezing. I didn't even try with 0.1million.

So, I decided to go async and used executeAsync alongwith Bind etc. This also shows visible freezing for just 20K records. This was the whole array being inserted and not in chunks.

Code: Select all

var dirs = Cc["@mozilla.org/file/directory_service;1"].
           getService(Ci.nsIProperties);
var dbFile = dirs.get("ProfD", Ci.nsIFile);
var dbService = Cc["@mozilla.org/storage/service;1"].
           getService(Ci.mozIStorageService);

dbFile.append('mydatabase.sqlite');

var connectDB = dbService.openDatabase(dbFile);

let insertStatement = connectDB.createStatement('INSERT INTO my_table 
                      (my_col_a,my_col_b) VALUES  
                      (:myColumnA,:myColumnB)');
var arraybind = insertStatement.newBindingParamsArray();

for (let i = 0; i < my_data_array.length; i++) {
  let params = arraybind.newBindingParams();

  // Individual elements of array have csv

  my_data_arrayTC = my_data_array[i].split(',');

  params.bindByName("myColumnA", my_data_arrayTC[0]);
  params.bindByName("myColumnA", my_data_arrayTC[1]);

  arraybind.addParams(params);
}

insertStatement.bindParameters(arraybind);

insertStatement.executeAsync({
  handleResult: function(aResult) {
    console.log('Results are out');
  },
  handleError: function(aError) {
    console.log("Error: " + aError.message);
  },
  handleCompletion: function(aReason) {
    if (aReason != Components.interfaces.mozIStorageStatementCallback.REASON_FINISHED)
      console.log("Query canceled or aborted!");
    console.log('We are done inserting');
  }
});

connectDB.asyncClose(function() {
  console.log('[INFO][Write Database] Async - plus domain data');
});   

Also, I seem to get the async callbacks after a long time. Usually, executeSimpleSQL is way faster than this.If I use SQLite Manager Tool extension to open the DB immediately this is what I get ( as expected )

Code: Select all

SQLiteManager: Error in opening file mydatabase.sqlite - either the file is encrypted or corrupt
Exception Name: NS_ERROR_STORAGE_BUSY
Exception Message: Component returned failure code: 0x80630001 (NS_ERROR_STORAGE_BUSY) [mozIStorageService.openUnsharedDatabase]

My primary objective was to dump data as big as 0.1 million + and then later on perform reads when needed.

[lithopsian]

Writing to the database using mozIStorage is low. At the end of each write (actually each transaction), it syncs the data to disk to guarantee integrity. You can sometimes improve the performance of multiple writes by wrapping them in a transaction to avoid multiple sync operations, but ultimately they will get written to disk. You could try explicitly running the database operations in a worker to keep them completely away from the main thread.

You might also check how long the bind loop is taking. I assume you are looping through 20k objects and performing non-trivial actions. That could take quite a while. Or the bind statement itself might take a while. You might find (with async inserts inside a single transaction) that it is more effective to insert the rows individually. Alternatively, smaller inserts *not* in a transaction might paradoxically be faster, if your large insert is blowing past the journal and cache sizes.