CI_DB_result methods are time and space inefficient

[eluser]voidstar[/eluser]
PHP 5.2.6, CI 1.7.0 (DB_result.php unchanged in SVN 1631)

result_array() returns a copy of a CI_DB_result data member ($result_array). So a query like

Code:

$query = $this->db->query("YOUR QUERY");
$rows = $query->result_array();

produces two copies of each row ('field'=>'value' pairs) generated by the query. For any query, but particularly for a large query, this consumes twice as much space as one might expect, and, of course, it takes time to copy the array.

The situation is worse for first_row('array'), last_row('array'), next_row('array'), and previous_row('array'). These methods make an internal copy of the $result_array data member, e.g.

Code:

$result = $this->result($type);

and then return a single row from the copy. Clearly this unnecessarily consumes time and space. These methods could easily access the data member $result_array directly and avoid this expense.

Also, it would be helpful if the documentation encouraged the use of 'object' instead of 'array'. In PHP5+ the data member $result_object will be an array of object handles, so 'object' versions of these methods will manipulate and return handles, making them much less expensive in terms of time and space.

[eluser]gh0st[/eluser]
If there is a better way to do the above, please give an example.

For example, before CI I used to use the ez_mysql library by Justin Vincent.

And of course there is the ADODB library.

If there's a better way to do the search results, please show some examples.

[eluser]m4rw3r[/eluser]
Usually PHP makes a copy by reference, until the copy is changed - then the real copy occurs (so called copy-on-write).
So the same arrays won't take twice the space, as long as they are unchanged (after the copy has been made).

Example:

Code:

$a = array(1, 2, ... , 1475137425428354); // allocates a lot of memory

$b = $a; // doesn't allocate any memory at all (except for the reference)

$b[0] = -2; // allocates new memory for a copy of a, with one changed value
// or:
$a[0] = -5; // does the same as above, provided $b hasn't been changed

BTW, I'm doing a remake of CI's db for PHP 5, so if anybody has ideas of improvement, I'm all ears.

[eluser]TheFuzzy0ne[/eluser]
[quote author="m4rw3r" date="1234382538"]BTW, I'm doing a remake of CI's db for PHP 5, so if anybody has ideas of improvement, I'm all ears.[/quote]

http://ellislab.com/forums/viewthread/102489/

[eluser]voidstar[/eluser]
I did not know that PHP used COW. Cool!

I retract my complaint about the space/time inefficiencies in DB_result, although in my case COW doesn't help. In this particular application it's very convenient in the Model to modify a field in the database records before returning them to the Controller.

Thanks for the help!

[eluser]m4rw3r[/eluser]
Well, DB_result is still a bit inefficient, because it loops the resultset more than necessary.
But it is unavoidable in PHP 4 (solving it with iterator interface in PHP 5).

[eluser]m4rw3r[/eluser]
Is it really necessary to cache the result in the resultset?
How often does the user call result() more than once on the same resultset? I certainly don't.

(And don't blame it on "foreach() calls the result() method for each row", it doesn't (at least not on PHP 5))