mysql - CFSCRIPT 中的事务性能缓慢

Question

我最近注意到我们的产品有一些“短”邮政编码的错误邮政编码位置(纬度和经度坐标) - 即“AB10”而不是“ABD 1PT”等。

邮政编码数据库/表用于在 Google map 上生成图钉，我现在发现，当我们将短邮政编码合并到具有完整邮政编码的表中时，一些(大约 2200)输入错误经纬度绕错了。

显然这是一个简单的修复，所以我决定编写一个小脚本来处理不正确的值(基本上交换它们)。

这是我的:

<cfscript>

  /** Fetch the wrong postcodes data **/
  db  = "postcodes";
  sql = "
    SELECT
      postcode, longitude, latitude
    FROM
      postcodes
    WHERE
      longitude > latitude
  ";
  q    = new Query(sql = trim(sql), datasource = db);
  data = q.execute().getResult();

  if (structKeyExists(form, "execute")) {
    if (isQuery(data) && data.recordCount > 0) {
      transaction action="begin" 
      { 
        try {
          qUpdate = new Query(
            datasource = db, 
            sql = "UPDATE postcodes SET longitude = :longitude, latitude = :latitude WHERE postcode = :postcode"
          );
          for (x = 1; x <= data.recordCount; x++) {
            writeOutput("<p>" & data["postcode"][x] & "</p>");

            qUpdate.addParam(name = "longitude", value = data["latitude"][x], cfsqltype = "CF_SQL_DOUBLE");
            qUpdate.addParam(name = "latitude", value  = data["longitude"][x], cfsqltype = "CF_SQL_DOUBLE");
            qUpdate.addParam(name = "postcode", value  = data["postcode"][x], cfsqltype = "CF_SQL_VARCHAR");

            qUpdate.execute();
            qUpdate.clearParams();
          }
          transactionCommit();
        } catch (any e) {
          transactionRollback();
          writeOutput("<p>The database transaction failed, rolling back changes</p>");
          writeDump(e);
        }
      }
      writeOutput("#data.recordCount# postcodes have been updated");  
    } else {
      writeOutput("There were no incorrect postcodes found in the database");
    }
  }
</cfscript>
<cfoutput>
  <form name="update" action="" method="post">
    <input type="hidden" name="execute" value="1"/>
    <input type="submit" name="update" value="Update #val(data.recordCount)# Postcodes"/>
  </form>
</cfoutput>

<!--- <cfdump var="#data#"/> --->

脚本被包裹在一个事务中，因为我计划在实时服务器上运行它，但是在本地测试脚本后，它继续运行了一个多小时!

邮政编码数据库包含近 170 万条记录，只有三列都正确索引了 postcode, longitude, latitude 第一个查询返回正确的 2,200 个结果。

我检查了 ColdFusion 管理中的组件缓存设置，看看我的本地是否缺少它，但是它是打开的!

那么我的问题 - 为什么这需要这么长时间才能执行？

我们正在使用 mysql 和 ACF 9。

Answer 1

为什么要在 CF 中这样做？只需在 SQL 中完成所有操作，它会快得多。我不使用 mysql，但大概是这样的:

UPDATE postcodes 
SET longitude = newlongitude,
latitude = newlatitude 
FROM (SELECT latitude AS newlongitude, longitude AS newlatitude FROM postcodes 
         WHERE longitude > latitude)