[Deprecated] DMZ 1.6.2 (DataMapper OverZealous Edition)

[eluser]Jack Scott[/eluser]
I'm building an application that manages a lot of database records across several tables, and have ran into problems with DMZ running my app out of memory. DMZ seems to load all its query results into memory at once, which hits memory pretty hard when loading several tens of thousands of records. Is there a way to load DMZ query results one at a time, similar to what Active Record does, so I can manage my memory footprint a bit better?

I got my app working for now by freeing query results after processing and before starting a new query, something I probably should have been doing in the first place. Here's the technique I used:

Code:

$this->table->get();
foreach ($this->table->all as $record)
{
    # do some stuff
}
unset($this->table->all);

Thanks for the help,
Jack

[eluser]OverZealous[/eluser]
@Jack Scott

Please look back (or search) this forum. TheJim (I believe) had a solution that we discussed could be converted into an extension. It basically involves using get_sql and then manually converting each row to an object as it is looped. I had written some information about the limits of get_sql, so you'll need to find that.

The latest DMZ automatically frees result sets if they contain more than 100 rows. For efficiency, I don't bother for smaller queries. You can reduce this threshold, check out the config page in the manual.

I'm thinking of adding two new methods into DMZ when I work on some performance updates: get_raw, which returns the result set, and get_streamed (or something similar), which only instantiates each object as it is looped over. These should help with large datasets. The latter will be based on the code that I just mentioned above.

In either case, it probably isn't very efficient to use ActiveRecord and PHP to process thousands of rows, (much less tens of thousands) no matter what other code is being used on the front end. ActiveRecord loads all of the rows into an array, so you'll lose a lot of memory with large numbers of rows no matter what. You should see if you can use the database server to process these rows, so you aren't transferring all that data back and forth. Just my 2¢.

[eluser]macigniter[/eluser]
EDIT: Please ignore this post and forgive my stupidity ;-) Just found the solution...

[eluser]Conerck[/eluser]
Got a question...
Is there any good reason why $obj->count() clears the query?

The reason I ask is, because I'm working on pagination for a rather complex search query and I need to get the total amount of matches in the DB. Since $obj->count() clears the query I have to build it the whole query again for get() call. This seems unneccesary and 'dirty'.

The other option would be to just skip the count and fetch the entire result set and then count them with count($obj->all), but then I'm stuck with extracting the relevant DB entries for my current page, whereas I could have much rather let the DB handle that part with limit()...

Any elegant ideas to solve this?

[eluser]OverZealous[/eluser]
Please see the big yellow section at the top of the Count page in the manual.

Edit: By that, I mean, just run your query, and then get the number. Calling count has to clear the query, because you are running a COUNT(*) against the database. Running any query against the DB must clear the current set of instructions.

[eluser]Conerck[/eluser]
Right, but Why is that a *must*. I get that regular inserts, selects, updates and deletes should clear the query but a Count... I don't see the point.

Anyway... Say I run the full query (which potentially costs alot of memory), what would Be a good way to get, say, entries 140 to 160 from the resultsset for the pagination?

[eluser]OverZealous[/eluser]
It must because that's the expected result. If you do this:

Code:

// set up query
$count = $object->count();

// Later...
$object->where('fun', TRUE)->get();

You will get unexpected results on the second query, because it contains everything in the first query. DMZ clears the query data whenever a query is run on the database.

If you want both the full query count, and want a subset for pagination, you can either put your query building into a method and run through it twice, or you can do this:

Code:

$count = $object->get_clone();
// force $db to be by-value
$count->db = unserialize(serialize($count->db));
$total = $count->count();

The hack trick in the middle is to ensure that $db doesn't get cleared by the count(). This is actually a bug, I just discovered, because in DMZ 1.6, get_clone() should be doing this automatically. In the next version of DMZ, you will be able to skip that step (as it will be automatic).

Also note that if you have already added items to the select or order_by statements, your count query may return unexpected results.

[eluser]Oblique[/eluser]
Has anybody here migrated from dedicated table to itfk? Shame on me, can't figure out the query 8(

[eluser]OverZealous[/eluser]
@Oblique

If you are trying to shift values from a dedicated to an ITFK, and you already know your configuration, you could do something crappy but functional like this:

Note: foos has the ITFK, bars_foos is the join, and bars is the other table

Code:

UPDATE foos
SET foos.bar_id = (SELECT bar_id FROM bars_foos WHERE bars_foos.foo_id = foos.id)

I hate using subqueries unnecessarily, but sometimes they solve the problem quickest.

[eluser]Oblique[/eluser]
That worked ) you are overzealous indeed )