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.

DCoder · Accepted Answer · 2012-04-05T15:50:40.160

16

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:

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).
It makes escaping data and avoiding SQL injections a lot easier.

edited Apr 05 '12 at 15:50

answered Apr 05 '12 at 15:43

DCoder

12,962
4
40
62

I don't understand the use of the underscore on line 3. – Phillip Senn May 02 '12 at 20:45
@Pedro: it's part of [underscore.js](http://documentcloud.github.com/underscore/), `_(array).each` works just like `forEach`, executes the given function for every element in the `array`. – DCoder May 03 '12 at 04:03
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. – VikrantY Sep 08 '13 at 13:04
1

@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.) – DCoder Sep 08 '13 at 13:12
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). – VikrantY Sep 10 '13 at 11:33
Sorry, you're right, there is no parallelism here. Ignore that. – DCoder Sep 10 '13 at 12:22
1

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. – Gavin Pickin Feb 20 '15 at 00:06

Optimizing WebSQL Local Database Population

1 Answers1

Linked