优化WebSQL本地数据库群 [英] Optimizing WebSQL Local Database Population

问题描述

我正在尝试优化我的本地数据库在正在开发的Web应用程序中填充的速度。目前，它使用PHP访问数据库，然后使用Javascript将该数据插入本地数据库。

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.

问题是，除了几个条目之外的任何内容都会减慢它的速度我很确定这是因为它为每一行执行单独的SQL查询。我一直在阅读交易（提交和回滚以及什么不是），这似乎是一个答案，但我不完全确定如何实现它，甚至在哪里。

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;
}

我不知道交易需要什么（或者即使那是什么需要要完成）。有MSSQL访问数据，SQLite插入它和运行PHP代码的Javascript。

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.

推荐答案

我会用占位符准备一个查询，然后用正确的参数为每一行执行它。像这样的东西（JS部分，使用 underscore.js 进行数组助手）：

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]);
    });
});

编辑：带占位符的查询有两个主要好处：

a query with placeholders has two main benefits:

1. 它使数据库引擎更容易缓存和重用查询计划（因为你运行相同的查询一百次而不是一百次不同的查询）。


2. 它可以更轻松地转义数据并避免SQL注入。

这篇关于优化WebSQL本地数据库群的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！