title: Optimizing WebSQL Local Database Population
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is a sample of one of the functions that loads a particular table.</p>
  
  <pre><code>function ploadcostcodes()
  {
  $IPAddress = '';
  $User = '';
  $Password = '';
  $Database = '';
  $Company  = '';
  $No='';
  $Name='';
  ploadSQLConnection($IPAddress,$User,$Password,$Database,$Company);
  </code></pre>
  
  <p>// This Connects to the actual database where the information comes from.</p>
  
  <pre><code>$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;
  }
  </code></pre>
  
  <p>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. </p>