Third db question today: do folk know there is a (lightly loaded) '[PHP-DB] list' with
some v.competent members?
Hi Alexander,
----- Original Message -----
> Let me start by laying out some facts:
>
> SELECT e.NAME AS customer, g.NAME AS driver_lastname, g.FIRSTNAME AS
> driver_firstname, $CommonSQL, COUNT(g.NAME) AS number FROM ENTITY e, ENTITY
> g, EXPERTISE_FILE f, DAMAGE_CASE d WHERE (f.CUSTOMER = '$CustomerID') AND
> (e.ID = f.CUSTOMER) AND (g.ID = f.DRIVER) AND (d.OWNER_EXP_FILE = f.ID) AND
> (f.DATE_REPORT BETWEEN '$StartDate' AND '$EndDate') GROUP BY e.NAME, g.NAME,
> g.FIRSTNAME
>
> The query above is one of the statistics gathering queries in my application.
> This one will run flawlessly.
> If however I add this condition: "AND (f.DRIVER = '$DriverID')" before "AND
> (g.ID = f.DRIVER), the script will eat all CPU and RAM (and eventually swap
> aswell) on my PowerEdge 2500SC.
> If I run both queries from inside psql (I use PostgreSQL), they -both- (!)
> complete within split seconds...
>
> If I leave set_time_limit() on the default of 30 secs, my script will time
> out somewhere around these lines (though it seems like coincidence...):
> function CalcDenied
> (
> $Requested,
> $Agreed
> )
>
> {
> $Denied = $Requested - $Agreed; --> Timeout here
> if ($Denied < 0) --> Sometimes here
> {
> $Denied = 0;
> }
>
> return $Denied;
> }
>
> To me, this does not seem to make any sense, especially since the first query
> (without the additional condition) runs fine, whilst it has to return many
> more rows.
>
> Does anyone have any idea what could be going on?
=I've got this sinking feeling that suggests I'm missing something obvious, but ...
=Have you tried ECHOing those long SELECT statements in PHP to see how they resolve,
before throwing them at
PostGres? Have you tried putting an ECHO into CalcDenied() to ensure that you are
getting what you, er,
$Requested and that it agrees/$Agreed with what you expected? (sorry, had to ask) -
the "$CommonSQL" bothers me
for some reason...
=I don't use PostGres, but the clause that you introduce does not require another
index/table/resource, so on
the face of it I can see no problem with the SQL - except consider using:
GROUP BY customer, driver_lastname, driver_firstname
=Apart from the time-out point, why do you link the SELECT and the function - what
data passes between them (is
it ECHOed...)?
=Are you sure that the function is not being repeatedly called until the script times
out (default time is
30secs) because of some never-ending loop condition - how many times is it being
called per data-row? How many
data rows are we talking about here?
=Sorry if they're just 'shots in the dark'!
=Let me know,
=dn
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
