#1
  1. Advisor
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Stockholm, Sweden
    Posts
    446
    Rep Power
    14

    PHP pg_query UPDATE performance problems..


    I have a table in which I store filenames with owner-, date-information etc.

    These "real" files are stored in a directory from where I list and fetch the information of each file in the database-table.

    I open a directory-handle and list the files...
    For each file I check the database...

    The problem is NOT the file-functions...
    I've already tried to fetch all files in an array and then loop
    it and fetch information from the database...
    And neither the SELECT-query...

    It's the UPDATE query that is my problem...
    Really poor performance...

    WITHOUT Update-query:
    Page executed in 0.0457099676132 seconds

    WITH Update-query:
    Page executed in 5.61226499081 seconds

    Here is my code:
    PHP Code:
    <table align="center" width="450">
    <?php
    // database connection-information is include
    include ("functions.inc.php");
    // $threehoursago is a variable set in functions.inc.php
    // osign is set to nothing when time is "three hours after" opentime
    $updsql "UPDATE bbfiles_tbl SET osign='' WHERE opentime<='$threehoursago'";
    pg_query($dbconnect,$updsql);


    $dir_name $BBETCDIR;
    $dir opendir($dir_name);
    while (
    $file_name readdir($dir)) {
    if ((
    $file_name != "." && $file_name != "..") && (substr($file_name,-4) == ".cfg
    "
    ) || (substr($file_name,-3) == "tab") || ($file_name == "bb-hosts")) {
    if (
    $i++ % == 0)
    {
    $bg="#E8E8E8";}
    else
    {
    $bg="#DADADA";}

    $file $dir_name .$file_name;
    $filemod filemtime($file);
    $filemodtime date("Y-m-d  H:m:s"$filemod);

    $sql "SELECT opentime,osign FROM bbfiles_tbl WHERE opentime!='' AND filename='
    $file_name' AND bbd='$BBDOMAIN'";
    $res pg_query($dbconnect$sql);
    $open pg_fetch_row($res);
    printf("<tr><td bgcolor=\"%s\" width=\"200\"><font size=\"2\" face=\"verdana,tah
    oma\">%s</font\302\273</td><td align=\"center\"><a href=\"view.php?%s\" alt=\"Vi
    sa filen\"><img src=\"../bilder/view.gif\" border=0></a></td><td align=\"center\
    "
    ><a href=\"javascript:FEdit('%s')\" alt=\"Editera fil\"><img src=\"../bilder/mo
    dify.gif\" border=0></a></td><td align=\"center\"><a href=\"history.php?%s\" alt
    =\"Visa filhistoria\"><img src=\"../bilder/history.jpeg\" border=0></a></td><td
    align=\"center\"><font size=\"2\" color=\"red\" face=\"tahoma\"><b>%s</b></font>
    </td><td align=\"right\"><font size=\"2\" face=\"tahoma\">%s</font></td></tr>"
    ,
    $bg$file_name,$file_name$file_name$file_name,$open[1],$filemodtime);
    unset(
    $open[1]);
    }
    }
    closedir($dir)
    ?>
    </table>
    Last edited by johan; September 30th, 2003 at 05:52 AM.
    - [ If I cant fix it, ask MacGyver... ] -
  2. #2
  3. Advisor
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Stockholm, Sweden
    Posts
    446
    Rep Power
    14
    Just to isolate the problem I removed the WHERE-caluse in my query.

    $updsql = "UPDATE bbfiles_tbl SET osign=''";

    But still got these perofrmance problems...
    - [ If I cant fix it, ask MacGyver... ] -

IMN logo majestic logo threadwatch logo seochat tools logo