I have a script which sends a number of queries to an Oracle database. The script is very very slow.

When I log into the box and fireup sqlplus the queries return results quickly.

Thinking it was the rest of my script that was causing the slow down, I commented out the database lines and replaced them with code to assign fake data to my variables.

The code runs extremely fast when I do that.

The code that seems to be causing the slow down is:


   $SQL = "SELECT COUNT(ID) as IDCount FROM ACTIONS WHERE DATE2 >= TO_DATE('" . date("Y-m-d H:i:s", $from) . "','YYYY-MM-DD HH24:MI:SS') AND DATE2 < TO_DATE('" . date("Y-m-d H:i:s", $to) . "','YYYY-MM-DD HH24:MI:SS') AND ACTN_TYPE = 'web'";

$stmt = ociparse($connection, $SQL);
ociexecute($stmt, OCI_DEFAULT); 
OCIFetchInto($stmt, $row, OCI_ASSOC+OCI_RETURN_NULLS);

Am I doing something wrong to make it so slow? As I said above, I've tried to run the SQL in sqlplus and there is no noticable delay on displaying results.

I'm on RHEL3 with Apache2, and PHP 4.x. The Oracle database is 10g running on a Win2003 server.

When I try it directly with sqlplus, I'm using the same username/password and connecting to it from the same box as the script is on.

Thanks in advance.

    Try this instead...

    $sql = "SELECT COUNT(ID) as IDCount FROM ACTIONS WHERE DATE2 >= TO_DATE('" . date("Y-m-d H:i:s", $from) . "','YYYY-MM-DD HH24:MI:SS') AND DATE2 < TO_DATE('" . date("Y-m-d H:i:s", $to) . "','YYYY-MM-DD HH24:MI:SS') AND ACTN_TYPE = 'web'";

    $statement = oci_parse($connection, $sql);
    oci_execute($statement);

    while ($row = oci_fetch_array($statement)) {
    $item1 = $row['item1'];
    // and so on
    }

      If that doesn't work I'd say it is your connection from Red Hat to Windows....

        Originally posted by kidgeek_dfw
        Try this instead...

        while ($row = oci_fetch_array($statement)) {

        oci_fetch_array is PHP5 only. I'm on PHP4 🙁
        Good thought tho.

          I hate to say it, but I think PHP4 might be your problem.

          PHP5 is screaming fast with Oracle.

          Any chance you could upgrade?

            Originally posted by kidgeek_dfw
            I hate to say it, but I think PHP4 might be your problem.

            PHP5 is screaming fast with Oracle.

            Any chance you could upgrade?

            Not for a while I'm afraid. :xbones:

              Write a Reply...