2

I have a many-to-many mapping between profiles and categories. This query:

$profiles = new Profile();  
$profiles->where('foobar_flag',1);
$profiles->where_related($category);
$profiles->get();

Is taking almost 30 seconds to run, with about 1000 entries in that profiles table. There is also a category table, and a profiles_categories table. I end up with a staggering 4000 queries per execution.

How do I make this faster?

Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
djvs
  • 552
  • 2
  • 11
  • We may need to see more code than that. Can you at least isolate *some* of the queries, or does this *only* happen using the `where_related()` line? Are you doing anything in the `__construct()` of your models? How is `$category` created? How many results do you expect, 1000? – Wesley Murch Sep 24 '11 at 05:20
  • where_related increases the number of queries by 10.7x, somehow. The construct methods are essentially empty. $category is just iterated in an array of categories. – djvs Sep 24 '11 at 05:33
  • You might be better off creating an array from `$category` ids and doing a `where_in()` on the join table. I don't want to add that as an answer because you shouldn't have to do stuff like that with an ORM, but I don't know if this is a shortcoming of Datamapper or not. – Wesley Murch Sep 24 '11 at 05:35
  • I was wondering if that was possible. – djvs Sep 24 '11 at 05:36
  • I'd say it's a good idea to get the queries down, but there might be a more appropriate "DM" way to do it. I side-step Datamapper sometimes for certain things and just use Active Record, but it sucks having to do it, like hard-coding or otherwise manually constructing the name of the join table for an AR query. – Wesley Murch Sep 24 '11 at 05:38
  • Datamapper has many `where_related` methods ( http://datamapper.wanwizard.eu/pages/getadvanced.html ) that might optimize this a bit, but it's tough to say what might help. Could you offer more info? Maybe run a `$profiles->check_last_query()` and let us see the actual query - or put up a test page with `$this->output->enable_profiler(TRUE);` and give us the link. – swatkins Sep 30 '11 at 19:17
  • 1
    Please revise your question, you show **ZERO code**, not a constructive question, marked for **CLOSE**. – Jakub Oct 11 '11 at 20:11
  • Add more code, and example data. – Gustav Bertram Nov 08 '11 at 09:07

1 Answers1

0

If you are unhappy with a function in datamapper, either find a simplified way of doing it as your active record query might just be too costly as you say.

Always run your profiler:

$this->output->enable_profiler(TRUE);

This will give you a true idea of what is being done behind the scenes. Optimize from there on. Otherwise we need more code to go on here to give relevant answers.

Jakub
  • 20,418
  • 8
  • 65
  • 92