First call to $query->next_row() loads all results

[eluser]BlueCamel[/eluser]
I've found that using the mysqli or mysql driver, that the $query->next_row() call loads all results from the db. This isn't desirable with large result sets as it consumes memory. For example, with a result set of 4.2 million records, there doesn't appear to be a good way to iterate over them without loading them all.

Code:

$this->db->from($large_table);
$query = $this->db->get();
if (!$query) show_error('Failed DB query');

while ($row = $query->next_row())
{
    // the above next_row() call loaded all results as seen when dumping the $query object
    print_r($query);
    break;
}

$query->free_results();

How would you loop through a large result set advancing the cursor without loading the entire set into memory?

[eluser]WanWizard[/eluser]
Some optimisation might be in order here, I ran into this issue as well.

From looking at the database driver code I assume this is done to keep to code generic, not all database platforms allow direct access to every record in the result set.

Having said this, the driver supports a _data_seek() method, so it would be quite easy to check if direct positioning is possible. If not, the seek would return FALSE, and you could fall back to reading the entire result set, and do your positioning using an array (which is what the code now does). Modifying this would mean changing a core library, or rewriting DB_result.php and finding a way of extending it.

[eluser]BlueCamel[/eluser]
Doing some debugging, I think this is caused the the num_rows() call found in the DB_result.php functions result_object() and result_array(). Showing just result_object() below, you can see where the $this->num_rows() is called early on. This forces the mysqli/mysql driver to fetch all results in order to get a total count.

I suspect other DB drivers do it similar. That is, you can't count a result set until after it's been loaded from the DB.

Code:

/**
         * Query result.  "object" version.
         *
         * @access      public
         * @return      object
         */    
        function result_object()
        {
                if (count($this->result_object) > 0)
                {
                        return $this->result_object;
                }
                
                // In the event that query caching is on the result_id variable
                // will return FALSE since there isn't a valid SQL resource so
                // we'll simply return an empty array.
                if ($this->result_id === FALSE OR $this->num_rows() == 0)
                {
                        return array();
                }

                $this->_data_seek(0);
                while ($row = $this->_fetch_object())
                {
                        $this->result_object[] = $row;
                }
                
                return $this->result_object;
        }

[eluser]BlueCamel[/eluser]
As a hack of a work around, you can get the db driver query result id from the $query->result_id property and supply it to the mysqli fetch_object() function:

Code:

$query = $this->db->get();
if ($query) show_error('bad query');

$count = 0
while ($row = $query->result_id->fetch_object())
{
    // execute code here
    $count++;
}
$query->free_results();

Of course, this negates the entire purpose of using the CI DBI layer.

[eluser]WanWizard[/eluser]
Attached you'll find a modified version of the DB_result library, which uses result set direct access if the database driver supports it, and falls back to CI behaviour if not.

Note that my prefix is CMS_, you might have to rename the file to get it to work in your CI installation. You place this file in your application/libraries directory.

Besides this, you have to modify the CI DB_driver library, so that it will load our custom result library, replace the load_rdriver() method with:

Code:

/**
     * Load the result drivers
     *
     * @access    public
     * @return    string     the name of the result class
     *
     * NOTE: Modified by ExiteCMS to allow loading of a custom DB_result library
     */
    function load_rdriver()
    {
        $driver = 'CI_DB_'.$this->dbdriver.'_result';

        if ( ! class_exists($driver))
        {
            $db_result = APPPATH.'libraries/'.config_item('subclass_prefix').'DB_result'.EXT;
            if ( file_exists($db_result) )
            {
                include_once($db_result);
            }
            else
            {
                include_once(BASEPATH.'database/DB_result'.EXT);
            }
            include_once(BASEPATH.'database/drivers/'.$this->dbdriver.'/'.$this->dbdriver.'_result'.EXT);
        }

        return $driver;
    }