Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Slow performance of the code, Can you help me out?

    I am having one client for whom I am developing this site and facing a low speed problem for generating the search results. I am posting the concerned code, for the page named search.php which is fired on clicking on search FORM a page named searchf.php, from which the varibales are gathered by the POST method of the form. Now can you please have a look at the code and suggest me or guide me to get optimal speed. Now I am getting the speed is normally it takes about 10 to 15 seconds to generate the page and display the results then clicking on the pages are displayed at a good speed, SO MY QUESTION IS THAT HOW TO SPEED UP THE PAGE GENERATION which can be done in either 2 to 5 seconds. CAN YOU PLEASE HELP ME OUT?

    I have not been able to post the whole code because it was exceeding the allowed length of the message.
    The Code goes like wise.
    _______________________________________________________________
    <?php
    session_start();
    $included_files = get_included_files();
    foreach ($included_files as $filename)
    $SFile=substr($filename,strrpos($filename, "\\")+1);
    ?>
    <?php include ("db.php") ?>
    <?php
    ob_start();

    if(!isset($pos))
    $pos=0;

    if(!isset($SelfCall))
    $SelfCall=0;

    if(!isset($_SESSION['$Qu']))
    $_SESSION['$Qu']="";

    if(!isset($Quprop))
    $Quprop="";

    if(!isset($_SESSION['$Quauc']))
    $_SESSION['$Quauc']="";

    if ($_GET['Vw']<>"")
    $View=$_GET['Vw'];


    $SelfCall=$_GET['SC'];

    // echo $SelfCall;
    // echo $_SESSION['$Qu'];
    $pos = $_GET['nav'];
    // echo $pos;
    if ($_COOKIE['cno']){
    if ($SelfCall==0)
    {
    if ($_REQUEST['view']){
    // echo $_SESSION['$Qu'];
    $req = $_REQUEST['view'];
    $View = 'D';
    $_SESSION['$Qu'] = "select * from port,portlots,property,adates,auctioneer where port.portno = $req and (port.portno = portlots.portno) and (portlots.pno = property.pno) and (property.ctno = adates.ctno) and (adates.acrcd = auctioneer.acrcd)";
    }
    else{
    //Starting to gather the data from the inputs.
    for ($i=1;$i <=2;$i++){
    $field = 'Tenure'.$i;
    if ($_POST[$field] !="")
    $Tenarr[] = "tenure = '". $_POST[$field] . "'";
    }

    for ($i=1;$i <=3;$i++){
    $field = 'Tenancy'.$i;
    if ($_POST[$field] !="")
    $Tenaarr[] = "Tenancy = '". $_POST[$field] . "'";
    }


    if ($_POST['AllResi'])
    {
    $Resiarr[] = " (property.typecd >=1 and property.typecd <=8) ";
    }
    else
    {
    for ($i=1;$i <=8;$i++){
    $field = 'Resi'.$i;
    if ($_POST[$field] !="")
    $Resiarr[] = "property.typecd = '". $_POST[$field] . "'";
    }
    }

    if ($_POST['AllCom'])
    {
    $Comarr[] = " (property.typecd >=9 and property.typecd <=27) ";
    }
    else
    {
    for ($i=9;$i <=27;$i++){
    $field = 'Com'.$i;
    if ($_POST[$field] !="")
    $Comarr[] = "property.typecd = '". $_POST[$field] . "'";
    }
    }


    if ($_POST['AucPeriod']==2){
    $frmon = $_POST['FrMon'];
    $fryr = $_POST['FrYr'];
    $tomon = $_POST['ToMon'];
    $toyr = $_POST['ToYr'];

    echo ("'" . $fryr . "/" . $frmon . "/" . "01'" > "'" . $toyr . "/" . $tomon . "/" . "01'");
    if ("'" . $fryr . "/" . $frmon . "/" . "01'" > "'" . $toyr . "/" . $tomon . "/" . "01'")
    $arr[] = "odates >= '".$toyr ."/". $tomon."/"."01' and odates <= '" . $fryr."/".$frmon."/"."31'";
    else
    $arr[] = "odates >= '". $fryr."/".$frmon."/". "01' and odates <= '" .$toyr ."/". $tomon."/"."31'";

    }

    if ($_POST['Street'] !="")
    $arr[] = "street like '%". $_POST['Street'] . "%'";

    if ($_POST['Town'] !="")
    $arr[] = " property.town like '%". $_POST['Town'] . "%'"." or property.county like '%". $_POST['Town'] . "%'";

    if ($_POST['PostCode'] !="")
    $arr[] = "property.postcode LIKE '%". $_POST['PostCode'] . "%'";

    if ($_POST['County'] !="")
    $arr[] = "property.county = '". $_POST['County'] . "'";

    if ($_POST['Region'] !="")
    $arr[] = "property.region = '". $_POST['Region'] . "'";

    if ($_POST['Auctioneer'] !="")
    $arr[] = "adates.acrcd = '". $_POST['Auctioneer'] . "'";

    if ($_POST['SoldStatus'] !="")
    $arr[] = "result = '". $_POST['SoldStatus'] . "'";

    if ($_POST['Income'] !="")
    $arr[] = "income = '". $_POST['Income'] . "'";

    $View = $_POST['View'];

    //finished gathering the data.

    $_SESSION['$Quauc'] = "select distinct auctioneer from auctioneer left join adates on adates.acrcd=auctioneer.acrcd left join property on adates.ctno=property.ctno ";
    //displaying the gathered data.
    $_SESSION['$Qu'] = "select property.pno,pictqty,picture,property.lotno,property.street,property.town,property.county,property.p ostcode,property.descr,property.typecd,property.tenure,property.tenancy,property.guideprice,property .income,proptype.type,proptype.Category,adates.odates,adates.time,auctioneer.auctioneer,auctioneer.t elephone from property left join adates on adates.ctno=property.ctno left join proptype on proptype.typecd=property.typecd left join auctioneer on adates.acrcd = auctioneer.acrcd ";
    $Quprop= " where (";

    for ($i=1;$i <=count($Tenarr);$i++){
    // echo 'Tenarr[' .$i. '] = ' .$Tenarr[$i-1];
    $Quprop = $Quprop . $Tenarr[$i-1];
    if ($i < count($Tenarr))
    $Quprop = $Quprop . ' or ';
    else
    $Quprop = $Quprop . ') and (';
    // echo '<br>';
    }

    for ($i=1;$i <=count($Tenaarr);$i++){
    // echo 'Tenaarr[' .$i. '] = ' .$Tenaarr[$i-1];
    $Quprop = $Quprop . $Tenaarr[$i-1];
    if ($i < count($Tenaarr))
    $Quprop = $Quprop . ' or ';
    else
    $Quprop = $Quprop . ') and (';
    // echo '<br>';
    }


    for ($i=1;$i <=count($Resiarr);$i++){
    // echo 'Resiarr[' .$i. '] = ' .$Resiarr[$i-1];
    $Quprop = $Quprop . $Resiarr[$i-1];
    if ($i < count($Resiarr)){
    $Quprop = $Quprop . ' or ';
    }
    else{
    if (count($Comarr)>0)
    $Quprop = $Quprop . ' or ';
    else
    $Quprop = $Quprop . ' ) ';
    }
    // echo '<br>';
    }

    for ($i=1;$i <=count($Comarr);$i++){
    // echo 'Comarr[' .$i. '] = ' .$Comarr[$i-1];
    $Quprop = $Quprop . $Comarr[$i-1];
    if ($i < count($Comarr)){
    $Quprop = $Quprop . ' or ';
    }
    else{
    $Quprop = $Quprop . ' ) ';
    }
    // echo '<br>';
    }

    if (count($arr)>0) $Quprop = $Quprop . " and (";
    for ($i=1;$i <=count($arr);$i++){
    // echo 'arr[' .$i. '] = ' .$arr[$i-1];
    $Quprop = $Quprop . $arr[$i-1];
    if ($i < count($arr))
    $Quprop = $Quprop . ') and (';
    else
    $Quprop = $Quprop . ')';
    // echo '<br>';
    }

    if ($_POST['Reposess'])
    $Quprop = $Quprop . " and ( (property.descr like '%by order%' or property.descr like '%by the order%') or (property.note like '%by order%' or property.note like '%by the order%') ) ";


    $_SESSION['$Qu'] = $_SESSION['$Qu'] . $Quprop ;

    $_SESSION['$Quauc']= $_SESSION['$Quauc'] . $Quprop . " order by auctioneer";

    //finished displaying gathered data.

    }
    $conn = mysql_connect(HOST, USER, PASS);//connection
    mysql_select_db(DB); //select of the database
    $rst = mysql_query($_SESSION['$Qu'], $conn);
    $tmp = $_SESSION['$Qu'] . " limit 0,7";
    $rs = mysql_query($tmp, $conn);
    if (!$rst)
    $totalRecs=0;
    else
    $totalRecs = mysql_num_rows($rst);
    // echo $tmp;
    // echo '<br>';
    // echo 'Total Records : ' . $totalRecs;

    $rs1 = mysql_query($_SESSION['$Quauc'], $conn);
    if (!$rs1)
    $totalRecs1=0;
    else
    $totalRecs1 = mysql_num_rows($rs1);

    }
    else
    {
    // THIS ELSE IS FOR THE NAVIGATION TO BE EXECUTED.
    $conn = mysql_connect(HOST, USER, PASS);//connection
    mysql_select_db(DB); //select of the database
    // echo $_SESSION['$Qu'];
    $tmp = $_SESSION['$Qu']. " limit " . ($_GET['nav']-1) . ",7";
    $rs = mysql_query($tmp, $conn);
    $totalRecs = $_GET['tr'];
    $totalRecs1= $_GET['tr1'];
    // echo $tmp;
    // echo '<br>';
    // echo $totalRecs;
    }
    }
    else{
    header("location:login.htm?cf=$SFile");
    die();
    }

    ?>



    <script LANGUAGE= "JavaScript" SRC="functions.js"></SCRIPT>
    <script language="JavaScript" src="movrout.js"></script>

    <html><head>
    ....................................................................

  • #2
    New Coder
    Join Date
    Sep 2002
    Location
    UK
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Well the reason its taking so long is the number of loops and arrays your using, your burning memory and CPU cycles like their going out of fashion. What are you searching through exactly? files on a filesystem? database?

  • #3
    New Coder
    Join Date
    Sep 2002
    Location
    UK
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok I can see you are searching a database, is the table(s) you are searching indexed? indexing can seriously speed up performance as long as you have indexed it well. I normally index fields by the fields I use WHERE on most. So on a users table the ID is always indexed as its the primiary key but for logging in I create an index of username and password to aid selecting the username and password quickly.

    Oh yes using count() in this way is a no no.

    PHP Code:

     
    for ($i=1;$i <=count($Tenarr);$i++){ 

  • #4
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are right but I have lot many variables to check for its value. Even depending upon its value I have to generate the query also. My tables are having Index. Do you find any other way out to gather these values from the form and generate the SQL query depending upon the value.
    diamonddivyang

  • #5
    New Coder
    Join Date
    Sep 2002
    Location
    UK
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Right then how many are you collecting? and why?

  • #6
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    Basically this site is of property auction information.
    Almost 50 to 53 variables I have to check because I have the search form for the property. Now for that
    2 variables I have to check whether the property is Free or Leased.
    3 variables I have to check whether the property is Vacant, Investment, or Partly Vacant
    8 Variables for the Residential type of properties ( 8 different types)
    19 Variables for the Comercial type of properties (19 different types)
    5 variables (auction period, from month, from year, to month, to year) for auction period

    and so on so that all it comes to almost 50 to 53 vaiables.

    So now can you suggest how is that possible in the shortest way without sacrificing speed.

    I have to go thru these loops and the arrays. I have no other option (As far I have known.)

    And after collecting the appropriate variables I have to prepare the query, which in turn brings the property matching what the user has chosen.

    Can you please suggest, If you have any better idea to accomplish it in the shortest way WITHOUT AFFECTING THE SPEED.
    diamonddivyang

  • #7
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ya one more thing If you want to see this page itself you can visit this site.

    it's www.ukinfogroup.com
    over there you can click the search from the left menu. And you can have a view of the page. The detailed search will not work unless you login using the username and password. But you can make one by going in Free trial. And then you can surely click and search for the properties.
    diamonddivyang

  • #8
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You do have a test environment? If so, run your code there, and print out the generated SQL query. Copy that query and run it on your test database via phpMyAdmin. Observe whether how much it takes to execute the query. By looking at the various parts of your SQL query I got the notion that you're probably joining a little bit too much with the LEFT JOIN statements. Searching with "%LIKE%" is going to reduce performance as well.

    Your first objective is to find out which part of your script makes the page so slow. My idea is that it's the SQL query, but check it. Without any benchmarking, all optimization suggestions are either common sense or guesswork.
    De gustibus non est disputandum.

  • #9
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the test environment with phpmyadmin query takes no time to generate the results.

    With in say a second or half of second the results are displayed.

    So what in that case I can do???
    diamonddivyang

  • #10
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    And if that is the case, if query is taking long time to generate the results, then displaying the second page does not take that much time it is displayed with in no time.
    diamonddivyang

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In your test environment,

    a) Does the test database have the same amount of data as the live tables in its tables? Say you have 10 records in your test tables, and 1 million in your life tables, that will make up for huge difference in performance.

    b) Does the test database server run under the same load as the live server? I.e. comparable hardware, number of simultaneous connections.

    Maybe it's best for you to first nail down where your script is losing time, you can user PEAR::Benchmark for this task. But it works as well with calls to microtime() and tracking the difference between the calls.

    I did not fully understand your last reply, but I suspect that the next pages come up faster because you pull the results into the session.
    De gustibus non est disputandum.

  • #12
    New Coder
    Join Date
    Sep 2002
    Location
    UK
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It might be worth downloading a trail copy of Zend Studio Pro/Enterprise and using the performance profiler on the code to see where the slow code resides and use MySQL Query Browser and get it MySQL to EXPLAIN (sql command) to show you where the query is inefficient, it will also tell you if its effectively using the indices


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •