StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP script very slow when adding postgres query in a while loop
text
Body
copied!<p>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.</p> <pre><code>$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); </code></pre> <p>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?</p> <p><strong>UPDATE:</strong></p> <p>Here's the complete loop</p> <pre><code>$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)&gt;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")) &lt; strtotime(date("Y-m-t"))) { $compdate = date("Y-m-01", strtotime("-1 month") ); } else { $compdate = date("Y-m-01"); } if (strtotime($compdate) &gt; $max) { return false; } else { return true; } } unset($dates); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload