I’m doing lots (like millions) of active record inserts in a function. CI maintains the insert query after the insert is done, and the result is that I run out of memory before my function is complete. I dumped out $this after each insert, and I can see it growing each time with the insert info, so I’m fairly certain this is where the memory leak is.

If I was doing a query, I could free the memory after, like this:

However, the insert function doesn’t return the database object, instead it returns a TRUE/FALSE. So, doing this returns an error:

How can I free the memory used by an active record insert?

Thanks!

Try this before getting into your insert() logic:

$this->db->queries and $this->db->query_times gets built up by default in CI unless you say so otherwise.

There is no need to use free_result() as that isn’t the problem - its just that CI is saving all queries, which if you are running a LOT of queries (or just multiple queries with lots of data) you will of course run into memory problems.

This is awesome; thanks!

I searched the User Guide for this and couldn’t find it. Is there a list anywhere of other un-documented features (other than the source code)? I was kind of surprised this wasn’t in the User Guide, as what is there is excellent.

Bill H - 01 January 2010 06:55 PM

This is awesome; thanks!

I searched the User Guide for this and couldn’t find it. Is there a list anywhere of other un-documented features (other than the source code)? I was kind of surprised this wasn’t in the User Guide, as what is there is excellent.

Only one place that I know of - the source code

I found this out of curiousity at your question. I popped open my debugger (Netbeans with XDebug) and poked around. I figured it must be in the database part of the core somewhere, so I put a stop in on the insert() function and didn’t see anything in the code, but then looked through the set class variables and found $queries full of inserts, and just happened to see save_queries option, which doesn’t seem to be settable by config - only by a direct call, as far as I could tell.

I use to treat source code as relatively complex as this as a block box I wouldn’t dare bother to try to look into, but CI is surprisingly good in this regard and a debugger makes it manageable. Is fun

If you’re inserting data into the same table and the same columns, you can combine multiple value sets into one INSERT query.  You may not be able to use the Active Record schema to achieve the correct query structure, but you can always write your own.

Do a quick web search for inserting multiple rows with one query for your respective database.  I know for a fact MySQL and MSSQL (SQL Server) can do it.

Aken - yeah, my first thought was to reduce the number of separate insert queries being flung at the DB - millions of queries per page load suggests some re-engineering may be indicated.

Brian - love the discovery, and I’ve updated the SQL FAQ with this undocumented feature.  The only place you find it in the user guide is within the Changelog - which is probably not the best place for it.  Seemingly this feature arrived 12 months ago.

jedd - 02 January 2010 06:11 PM

Aken - yeah, my first thought was to reduce the number of separate insert queries being flung at the DB - millions of queries per page load suggests some re-engineering may be indicated.

For the record, this really isn’t a “per page load” sort of thing. We house a lot of customer data, which we lookup, display and modify in the normal way you’d think of using CI (i.e. a web interface, executing small lookups, inserts, updates, etc). However, we also do hard-core analysis and modification of the data, where we have complex queries and functions that run for many hours. We’re using CI for this as well, running it from the command line. Having a common framework let’s us have consistent coding and libraries across components and let’s us take advantage of CI’s features like active record, etc.

It helped me very much, @BrianDHall. Thanks a lot.

Is there a “right” way to set this to false? A method or a config item?

Does doing this I only loose the Profiler Query stuff or I loose something else?

Thanks

It’s a var in the DB_driver.php file!

InsiteFX

Yep. But it could be a method, or something. Like enable_profiler() do…

Doing it this way ($o->var = TRUE) I could set the var to anything I want…

Thanks.

BrianDHall - 01 January 2010 05:39 AM

Try this before getting into your insert() logic:

$this->db->save_queries = false; 

$this->db->queries and $this->db->query_times gets built up by default in CI unless you say so otherwise.

There is no need to use free_result() as that isn’t the problem - its just that CI is saving all queries, which if you are running a LOT of queries (or just multiple queries with lots of data) you will of course run into memory problems.

thanks.  that is awesome.  i was parsing three 300mb text files into a database, and couldn’t work out where the problem was that was generating the out of memory errors. 
i had tried loads of things, like sending a batch of 1000 rows at a time to the dbms to improve efficiency etc., unset all variables in the loop etc.
now the httpd is using 11mb, and everything is shiny