#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    99
    Rep Power
    2

    Multiples SELECT queries


    I have 2 select queries on a page. Each query has a load time of Query took 0.0005 seconds as investigated into phpmyadmin.
    But when i load the page itself it takes somewhat of 4-5 seconds to load the page.
    On closer inspection with web developer tools i can see that the document itself is taking this long.
    The images on the page are all cached and have very very little load time. Between 20-30ms
    The GET for the document itself shows as 4840ms and the wait time is 4754ms. The DNS time is 50ms, connect time is 12ms and TLS time is 24ms.
    Why would this happen ?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Posts
    8
    Rep Power
    0
    Please share what kind of page it is. Is it .php, .html, etc? Does it load external JavaScript and/or .css files? Is there internal script/css? There are many factors. The best concept I've learned in problems like this having uncertainty as to the cause is to "divide-and-conquer", well...no..."process of elimination". Create a new empty page, and gradually add the components in one-by-one until you've isolated the source of the problem. I like Mozilla's developer tools that are available in Firefox Developer edition. Check that out if you are unaware of it. But...my blind guess in the page is loading something from a resource that is taking a long time. Well, it would have to be that I suppose. Don't trust yet that the query is not the problem either. Test it some other way if you can. Good luck.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    99
    Rep Power
    2
    I did use Firefox Developer. And all resources are cached and not served when page loading. Only the document itself has that wait time. It is php file. And strange enough only on this page, the other pages don't have wait time this high.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    i'm going to move this thread to the php forum

    if it turns out it was a database problem, feel free to ask to have it moved back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    143
    Rep Power
    66
    You are going to have to specifically start measuring/profiling the time it takes for your server-side script to run in order to find where the problem is.

    Php has a $_SERVER['REQUEST_TIME'] variable that is the timestamp when the request was received by the server. You can use this and time() at the start and end of your code, to capture, calculate, and display the amount of time from when the request was received to when the php code started execution (this will be the time the server took before it invoked php and the time it took php to open, read, and parse the code) and the total time it takes for the server-side code to run.

    Report back with the values you get from doing this.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Posts
    8
    Rep Power
    0
    Yes, using the time() function in PHP is a great idea. And you can even use it in between actions that are occurring as the page processes to isolate further which action is taking too long. Also, don't forget: don't make ANY assumptions about what is taking place in all areas of the page processing and loading (such as cached resources--just everything: don't assume). The main thing I wanted to add here is try running different, simpler sql and if a simple version fixes the problem, then make it increasingly more toward what you need it to be and see if the monster raises its ugly head. Get the record ID (or primary key) of a record you know you want/need and add that to the sql so it drills down only to that record. If going straight for a single record fixes the problem greatly...well you have one improvement.
  12. #7
  13. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,276
    Rep Power
    4193
    I use a function like this to measure time between points in a script when trying to figure out what is causing slowness.
    Code:
    function markTime($label){
        static $points = [];
        static $firstRun = true;
    
        if ($firstRun){
            $firstRun = false;
            register_shutdown_function(function () use (&$points){
                $firstPoint = reset($points);
                $lastPoint = end($points);
    
                $firstTime = $firstPoint[1];
                $lastTime = $lastPoint[1];
    
                printf("<div class=\"timerBlock\" onclick=\"this.className += ' show';\">\r\n");
                printf("\t<p>Total Time: %0.3f seconds; Points: %d</p>", $lastTime - $firstTime, count($points));
                printf("\t<p>Points:</p>\r\n<ol>\r\n");
                $previousPoint = null;
                foreach ($points as $p){
                    printf("<li>%s: ", $p[0]);
                    printf("<span title=\"Since Start\">%0.3fs</span> ",
                        $p[1] - $firstTime
                    );
                    printf("<span title=\"Since previous\">(%0.3fs)</span>",
                        ($previousPoint) ? $p[1] - $previousPoint[1] : '-'
                    );
                    $previousPoint = $p;
                    printf("</li>\r\n");
                }
                printf("</ol></div>");
            });
        }
    
        $points[] = [$label, microtime(1)];
    }
    Just sprinkle calls to it throughout your code and look at the results.

    If the queries process quickly, but the page loads slowly then likely something you are doing in your result processing loop is causing the slow down.

    Are you by chance running queries within queries, such as:
    Code:
    $results = runFirstQuery();
    foreach ($results as $row){
        $row['blah'] = runAnotherQuery();
    }
    That is a common cause for slowness. If you are, you need to refactor the code and your queries to eliminate that, such as by using joins.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  14. #8
  15. No Profile Picture
    Super Moderator
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,567
    Rep Power
    112
    Sorry if I overlooked this, but is your shorter timing based on just a single run of the query itself, while the longer timing is the whole document? Is the query within a loop, or prior to? I've seen queries within loops, so 1 query run may count short of time, but the overall document takes longer time do to the short-timing loop being ran numerous times overall.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo