Optimizing WebSQL Local Database Population

Question

I am trying to optimize the speed that my Local database populates in a Web app that is being developed. Currently, it uses PHP to access the Database and then inserts that data into the local database using Javascript.

Problem is, anything more than a couple entries slows it down and I'm pretty sure it's because it executes an individual SQL query for EVERY row. I've been reading up on transactions (Commits and Rollbacks and what not) and it seems like an answer but I'm not entirely sure how to implement it, or even where.

Here is a sample of one of the functions that loads a particular table.

function ploadcostcodes()
{
$IPAddress = '';
$User = '';
$Password = '';
$Database = '';
$Company  = '';
$No='';
$Name='';
ploadSQLConnection($IPAddress,$User,$Password,$Database,$Company);

// This Connects to the actual database where the information comes from.

$Login = 'XXXXXXX';
$conn=mssql_connect($IPAddress,$Login,$Password);
 if (!$conn )
{
      die( print_r('Unable to connect to server', true));
}
 mssql_select_db($Database, $conn);

 $indent="        ";

$sql="SELECT Cost_Code_No as No, Description as Name, Unit_of_Measure FROM v_md_allowed_user_cost_codes WHERE Company_No = " . $Company . " and User_No = '" . $User . "'";

 $rs=mssql_query($sql);
 if (!$rs)
 {
   exit("No Data Found");
 }

 while ($row = mssql_fetch_array($rs))
 {
     $No = addslashes($row['No']);
     $Name = addslashes($row['Name']);
     $Name = str_replace("'",'`',$Name);
     $Unit = addslashes($row['Unit_of_Measure']);

  //THIS IS WHERE I SEE THE PROBLEM

     echo $indent."exeSQL(\"INSERT INTO Cost_Codes (Cost_Code_No,Name,Unit_of_Measure) VALUES('".$No."','".$Name."','".$Unit."')\",\"Loading Cost Codes...\"); \r\n";
 }
 mssql_free_result($rs);
 mssql_close($conn);
 return 0;
}

I don't know what needs the transaction(or even if that's what needs to be done). There is MSSQL to access the data, SQLite to insert it and Javascript that runs PHP code.

Ainsley

Answer 1

I would prepare a query with placeholders, then execute it for each row with the right arguments. Something like this (JS part only, using underscore.js for array helpers):

db.transaction(function(tx) {
    var q = 'INSERT INTO Cost_Codes (Cost_Code_No, Name, Unit_Of_Measure) VALUES (?, ?, ?)';
    _(rows).each(function(row) {
        tx.executeSql(q, [row.code, row.name, row.unit]);
    });
});

Edit: a query with placeholders has two main benefits:

1. It makes it a lot easier for the DB engine to cache and reuse query plans (because you are running the same query a hundred times instead of a hundred different queries once).
2. It makes escaping data and avoiding SQL injections a lot easier.

Anett

I don't understand the use of the underscore on line 3. – Oystercatcher

@Pedro: it's part of underscore.js, _(array).each works just like forEach, executes the given function for every element in the array. – Anett

What if one of the inserts fail, how would transactions be managed then. Since each executeSQL is Asynchronous, it might insert the other SQL before failing. Is there a way around that. – Capuchin

@VikrantY: yes. You can pass a success callback and an error callback to each executeSql call - the error callback can rollback the entire transaction. Instead of using each, you can create a chain of calls - create a function that cuts one row out of rows and executes the query for that row, the query's success callback calls that same function (so it recurs until rows becomes empty) and its error callback rolls back the transaction instead. (Note that waiting for each row to execute before starting the next one loses the speed of parallelism. Other solutions are possible.) – Anett

Thanks @DCoder. I achieved the above by using a JQuery Queue to make it simpler. But the concept is exactly what you mentioned. I am not sure about parallelism though. I thought JavaScript did not have threads hence there was no way for it to do things in parallel(With the exception of WebWorkers of course). – Capuchin

Sorry, you're right, there is no parallelism here. Ignore that. – Anett

FYI - if you want to know the speed difference with 1 transaction versus 1 transaction per query... I just tested both methods, with 500+ queries. I found that it was approx 3 seconds for 1 transaction, compared to 15 seconds with 500+ individual transactions. – Seismography