dongwen7730
dongwen7730
2013-07-07 10:04

在while循环中添加postgres查询时,PHP脚本非常慢

I have a PHP script that loops through 2000+- records uisng a while loop. Within this while loop a postgres sql query has to be performed, unfortunately it can't be excluded from the while loop.

$sql = "(SELECT (timestamp) AS time FROM followups as f 
          JOIN campaigns as c ON c.id = f.campid 
           WHERE c.clientid = ".trim($clientid)." AND c.contractno = '".trim($c)."' AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))) )
            UNION ALL (SELECT (timestamp) AS time FROM followups as f WHERE (contractno ='".trim($c)."' 
             OR contractno LIKE '%".trim($c)."||".trim($clientid)."%' 
              OR contractno = '".trim($c)."||".trim($clientid)."') AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X')))     ) 
                UNION ALL (select (f.timestamp) AS time FROM followups as f 
                 JOIN campaigns as c on c.id = f.campid WHERE c.clientid = ".trim($clientid)." 
                  AND c.clientid in ( 
                   SELECT id FROM easy_mapping where id = ".trim($clientid).") AND (LOWER(person) IN (SELECT LOWER(userid) 
                    FROM users WHERE type IN('S','X')) OR LOWER(person) IN 
                     (SELECT LOWER(name) FROM users WHERE type IN('S','X'))))";
$result = pg_query($conn,$sql);

The query above is included in the while loop, the first few records perform very quickly and then the script starts to slow down, taking almost a day to complete the script. Is there a way to write the exact query above differently to gain the same results?

UPDATE:

Here's the complete loop

$dates = array();
$clientid = str_replace("\t", '', $clientid);
foreach ($contracts as $c) {
    $c = str_replace("\t", '', $c);
    $sql = "(SELECT MAX(timestamp) AS time FROM followups as f
    JOIN campaigns as c ON c.id = f.campid
    WHERE c.clientid = ".trim($clientid)." AND c.contractno = '".trim($c)."' AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))) )
    UNION ALL (SELECT MAX(timestamp) AS time FROM followups as f WHERE (contractno ='".trim($c)."'
    OR contractno LIKE '%".trim($c)."||".trim($clientid)."%'
    OR contractno = '".trim($c)."||".trim($clientid)."') AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X')))     )
    UNION ALL (select MAX(f.timestamp) AS time FROM followups as f
    JOIN campaigns as c on c.id = f.campid WHERE c.clientid = ".trim($clientid)."
    AND c.clientid in ( SELECT id FROM easy_mapping where id = ".trim($clientid).") AND (LOWER(person) IN (SELECT LOWER(userid) FROM users WHERE type IN('S','X')) OR LOWER(person) IN (SELECT LOWER(name) FROM users WHERE type IN('S','X'))))";
    $result = pg_query($conn,$sql);
    if (pg_num_rows($result)>0) {
        while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
            if (empty($row['time'])) {
                continue;
            }
            $dates[] = $row['time'];
        }
    }
    pg_free_result($result);
}
if (empty($dates)) {
    return false;
} else {
    $max = max($dates);
    if (strtotime(date("Y-m-d")) < strtotime(date("Y-m-t"))) {
        $compdate = date("Y-m-01", strtotime("-1 month") );
    } else {
        $compdate = date("Y-m-01");
    }
    if (strtotime($compdate) > $max) {
        return false;
    } else {
        return true;
    }
}
unset($dates);
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douhuang5623 douhuang5623 8年前

    The following is the result of what I can understand from your really junkish code.

    $clientid = trim(str_replace("\t", '', $clientid));
    $sql = "
    select max(time)
    from (
    (
        select max(timestamp) as time
        from
            followups f
            inner join
            campaigns c on c.id = f.campid
            inner join
            users u on lower(f.person) in (lower(u.userid), lower(u.name)) 
        where
            c.clientid = $clientid
            and u.type in('S','X')
    )
    union
    (
        select max(timestamp) as time
        from
            followups as f
            inner join
            users u on lower(f.person) in (lower(u.userid), lower(u.name)) 
        where
            contractno like ('%' || $clientid || '%')
            and u.type in('S','X')
    )
    union
    (
        select max(f.timestamp) as time
        from
            followups as f
            join
            campaigns as c on c.id = f.campid
            inner join
            users u on lower(f.person) in (lower(u.userid), lower(u.name))
            inner join
            easy_mapping em on c.clientid = em.id
        where
            c.clientid = $clientid
            and u.type in('S','X')
    )) s
    ";
    $result = pg_query($conn,$sql);
    if (pg_num_rows($result) == 0) {
        return false;
    } else {
        $max = $row['time'];
        if (strtotime(date("Y-m-d")) < strtotime(date("Y-m-t"))) {
            $compdate = date("Y-m-01", strtotime("-1 month") );
        } else {
            $compdate = date("Y-m-01");
        }
        if (strtotime($compdate) > $max) {
            return false;
        } else {
            return true;
        }
    }
    pg_free_result($result);
    
    点赞 评论 复制链接分享

相关推荐