加快通过Select语句循环的SQL更新语句 [英] Speed Up SQL Update Statement That Is Looped Through A Select Statement

问题描述

在代码中找到需要31分钟(1900秒)才能完成的SQL查询.首先，一条select语句将抓取1955行，然后代码循环遍历这些行，以基于记录集内的数字运行更新.它运行的表有14,000行.我该如何加快速度?

Found a SQL query in code that takes 31 minutes to complete (1900 seconds). First a select statement grabs 1955 rows, then the code loops through these rows to run an update based on a number inside that recordset. The table it runs through has 14,000 rows. How can I speed this up?

$sql = "select id, did, customer_id from dids";
        $rs = $db->PDOquery($sql, $qry_arr);
        //Loop through all DIDs and attach to cdrs  select id, did, customer_id from dids   
        while($row=$rs->fetch()){                           
            $qry_arr = array(':did_id' => $row['id'],
                        ':customer_id' => $row['customer_id'],
                        ':did' => $row['did']);
            $sql = "update ".$billing_table."  c ";
            $sql .= "set c.did_id = :did_id, c.customer_id = :customer_id  ";
            $sql .= "where c.customer_id = 0 and c.telcom_num = :did ";

            $result=$db->PDOquery($sql, $qry_arr);
            set_time_limit(30);  //Reset time limit after each query
            if (!$result) {
                error_log(date("Y/m/d h:i:sa").": "."\nError In Sql.\n".$sql, 3, $cron_log);
            }
        }

尝试使用以下方法，但显示错误代码:1054."where子句"中的未知列"dids.did"

Tried using the following but get an error saying Error Code: 1054. Unknown column 'dids.did' in 'where clause'

 UPDATE ".billing_table." SET ".billing_table.".did_id = dids.id, ".billing_table.".customer_id = dids.customer_id WHERE dids.did =  ".billing_table.".telcom_num

推荐答案

对SQL查询进行序列化通常会导致性能下降.您可以在一条语句中完成全部操作:

Serializing the SQL queries often leads into bad performance. You can do all in one statement:

$sql = "update ".$billing_table." c ".
       "inner join dids d on d.did=c.telcom_num ".
       "set c.did_id = d.id, c.customer_id = d.customer_id ".
       "where c.customer_id = 0;";

这篇关于加快通过Select语句循环的SQL更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！