Unless we do a mysql_unbuffered_query, mysql_query always put query results somewhere in server’s memory. I was looking at the code in DB_result.php and notice that it always copy the result set to an array. By this way, isn’t it duplicating the data in memory?

I think CI uses this approach because the arrays are the only way to send data to the views. Or not? But think about a very large result set. Why we need to have two copies of it in memory (array and buffer), if we already have copied it to an array? Shouldn’t the functions that fetch data to arrays automatically free the results from the buffer, as they will not be needed anymore? Or CI does this already?

In my own library, I never retrieve result sets to arrays as I always direct them to where they was needed. For example, after fetching a row, I forward each field to my own template parser. Well, I agree that this may not be the perfect way to separate application logic from presentation logic, because the loops goes with the application. But in this case, I never needed to make copies of the result sets to arrays. But with Code Igniter I think this is not possible, unless if have a way to place a function that act like mysql_fetch_array() into the views.

Well, I new here and I want to know what is the best way to do the things. Don’t this copy of result sets to arrays affect the server performance anyhow? Or are they absolute necessary to use all the power or Code Igniter?

I think the array you are talking about is CIs db cache. In the next version you are able to turn the caching off.

Arrays are not the only way to send data to the views. Arrays are used for backward compatibility with php4. In php5 you can loop through objects not in php4.

No, it is the arrays or objects you use in your application to retrieve SQL results. For example:

What $query->result() does at it first call is to fetch all rows from the query and put them into an array or object, which is handled by the function foreach(). But looking at the code, I saw that $query->result() could automatically free the SQL result since it copy all rows to an array or an object.

In the old days, when we put everything in the same PHP file, we could query a database and then fetch a row and print it, fetch another row and print it, and so on. Nothing need to be copied to arrays. With a template parser you could do the same, if the loop control be at your controller, not in the template. But with views, how can we make it get data direct, without pass data by an array or an object?

If there is no another way, what I wonder is: why to keep two copies of the same results in memory? Couldn’t the result set be free after the copy? And this copy process, in the case of big result sets, isn’t it a drawback?

Nobody here have an opinion about this?

I’ve been annoyed by the same feature as well. On a project where I needed to retrieve really large result sets, the script would crumble and I needed to do a work-around.

So what I did was calling _fetch_assoc or _fetch_object directly in the controller/model, which isn’t exactly good code practice. A more elegant solution would definitely be preferred.

Almost everywhere we exchange data from one class to another using arrays, so is essential to care and not use more memory than necessary. Where is possible, use instances to referer data instead of copy it.

I think db->result_object() and db->result_array() could free the results after they copy the result set to arrays and then release memory. But, if they do that, if you use db->result_object(), you will cannot use db->result_array() and vice versa.

The solution, in my opinion is to automatically free the results as an option in the config file. If you choose turn it on, you will must to use a preferred method (result_object or result_array).

the reason that result_object and result_array do not free the query results before returning their data is probably due to the way the who result object is created i think.
if you use the helper functions to move back and forth through the dataset it actually grabs all of the data and store it in the result object and then just uses an internal pointer to move back and forth
so if figure the reason that the free_results might not be done after a result_object or result_array is that you would not then be able to move back and forth using the result method

now I know you might not want to move back and forth after calling result_object etc because you have all of the data in it in separate array, but the problem is that because the data is cached the way it is it allows multiple calls to result_object without having to requery. free_result would remove that cache as well.

basically Im sure it can be considered but it might not be a simple fix because of the nature of how things are coded in the result object

Take a look at the result_object function:

And at the result_array() function:

As you can see, it doesn’t matter how many times you call the result_object(). It will get the result data only once and copy it to an array. After that, you can do the forward or backward how many times you wish, because it will be done in the array already built.

If I put a mysql_free_result() at the end of the function, it will not affect anything, because the data is already in the array. The only thing that will not work is if I switch between result_object() and result_array() in my code, because they produces different data and stores it in different arrays. So, if I free the results at result_object(), I will cannot get them at result_array() and vice versa.

The best option, in my opinion, could be call $this->_fetch_object() or $this->_fetch_array() directly into the views. Such functions will load the data directly from the result set instead of copy them into an array. As the data is already in memory, you will save a lot of memory in big result sets and prevents script crashing with memory overflow. Considering a high load web site, with thousands of simultaneous users, any byte you save can make a big difference.

Well, I am not here to suggest the right changes. I am just pointing something I think that could be better.

I understood what the result_array and result_object methods do because I have looked into them just recently
I was not sure what the free_result actually did from a code point of view.
Having now looked the only issue I can see with this is that, at least in the mysql result object, it clears the result and unsets the result_id. Without the result_id you cannot use num_rows or num_fields in their current implementations because for the MySQL driver this uses PHP Mysql functions, rather than looking at the cached result data

So I guess that is the main issue at this stage to implementing this.

As I have said I understand what you are trying to do, it is something I am always conscious of, and that is resource, in particular memory, management.

[EDIT]
What I would really like to see is the ability to extend the database classes somehow so that we could implement some of these changes our selves if we really want them
i.e. If I have an app I know will never use num_rows or num_fields then I could extend result_array so that it did do a free_result afterwards and cleanup the resources.

Without the result_id you cannot use num_rows or num_fields in their current implementations because for the MySQL driver this uses PHP Mysql functions, rather than looking at the cached result data

In my own database class I used to save the num_rows and affected_rows in object properties, right after I run queries. So, they are there while the object exist. My class was something like this:

I’m waiting for this feature too, because i’m dealing with a lot of data in the database.
I did not look into the CI code but it seems like CI copy the result without freeing it?

Yes, it did copy the result sets to arrays and do not free it from memory with mysql_free_result(). Because if it does, you should use only $this->db->result() or $this->db->result_array(), never both. So, you stay with two copies of the same results in memory.

You must free the results with $this->db->free_result(), just after you call $this->db->result() or $this->db->result_array(). And you must rememeber that if you use one function, you cannot use the another one, because both functions copy the result set from the result memory to arrays. If you need to use both, place your $this->db->free_result() after them.

Another workaround is not use $this->db->result() or $this->db->result_array(). You can call the function $this->db->_fetch_assoc() or $this->db->_fetch_object() directly into your views, where you will loop throught the result set. By this way, the result sets will not be copied to arrays. You will have one less loop to be executed for each database query and will save more memory. These simple things in a high load web server means a lot.

Having to double-up or read a set of results twice just to get the data is a known limitation of CI, and MVC in general. The only way around that is to loop through the records directly.