#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    $SQL->rowCount(); is very expensive! What to do?


    Hello in a query that returns quite few (4 million) records, rowCount(); takes the same amount of time as fetchAll(PDO::FETCH_ASSOC); (which is a bit unexpected as rowCount(); does not return any values).

    What is a less expensive way of counting rows?
    PHP Code:
    $page_load_time microtime(true);
    echo 
    "<br />Line:" __LINE__ ", Time:" number_format(microtime(true)-$page_load_time,2) . "<br />";
    ini_set('memory_limit''-1');
    include 
    "/home/includes/connections.php";
    $query "
    SELECT packages.id              AS PACKAGE_ID,
           annual_calendar.day_date AS DAY_DATE,
           allotments_new.day_date  AS ALLOTMENTS_DAY_DATE
    FROM   packages
           INNER JOIN hotels
                        ON hotels.hotel_id = packages.hotel_id
                        AND hotels.status= 'active'
            LEFT OUTER JOIN allotments_new
                        ON allotments_new.package_id = packages.id
                        AND allotments_new.day_date <=:next_year
           LEFT OUTER JOIN annual_calendar
                        ON annual_calendar.day_date = allotments_new.day_date
                           AND annual_calendar.day_date <=:next_year
    WHERE  packages.status = 'active'
    ORDER BY packages.id DESC
    "
    ;
    $arg = array('next_year'=>date('Y-m-d'strtotime('+365 days')));


    echo 
    "<br />Before num rows Line:" __LINE__ ", Time:" number_format(microtime(true)-$page_load_time,2) . "<br />";
    $number_of_rows DB::Load()->Execute($query,$arg)->returnNumAffectedRows();
    echo 
    "<br />After num rows Line:" __LINE__ ", Time:" number_format(microtime(true)-$page_load_time,2) . "<br />";
    $packages DB::Load()->Execute($query$arg)->returnArray();
    echo 
    "<br />After return Line:" __LINE__ ", Time:" number_format(microtime(true)-$page_load_time,2) . "<br />"
    Line:2, Time:0.00

    Before num rows Line:27, Time:0.00

    After num rows Line:29, Time:13.55

    After return Line:31, Time:31.75
    Last edited by zxcvbnm; December 10th, 2012 at 11:20 PM.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    1. Where are you calling rowCount()?
    2. How does it work?
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by requinix
    1. Where are you calling rowCount()?
    2. How does it work?
    1 - $number_of_rows = DB::Load()->Execute($query,$arg)->returnNumAffectedRows();

    2 - I use Northie's class.
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    SELECT COUNT(*), that returns the count in a single row, without forcing PHP to chunk through every row just to count them.

    Or use SQL_CALC_FOUND_ROWS, but that's harder.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

IMN logo majestic logo threadwatch logo seochat tools logo