cheesegrits
Support Gopher
OK, as I said in my first response, the problem is that you are using a 'value' for the CDD that isn't unique (like the PK). The field you are using (ITO) is very non-unique, in other words there are many rows in the joined table that share that value. For example, picking "BIKAS, Vasileios" at random, there are 9 rows in that joined nominations table with that ITO.
A CDD is just like a database join - the 'value' (FK, foreign key) you use MUST be unique, so there's only one row in the joined table with that FK. So that the table join between the two tables only yields one row ... "LEFT JOIN othertable.pk = thistable.fk" ... so that relationship can only ever be a single row.
So this means that in list view, when we run the query, instead of selecting the 10 (or whatever) rows being displayed, the LEFT JOIN turns that into 100 or 500 or 1000 (depending how many duplicates of the FK are in the joined table). And because you have multiple joins, that effect gets compounded, into (probably) 10's of thousands of rows being selected ... which we then have to reduce down. And the effect on memory and processing is geometric. So with (say) 50 rows in your main table, you might not notice, but at 500, the effect is huge.
The only way to fix it is to use the 'id' as the value for the CDD. But that means modifying all your existing data, to convert existing 'ITO' value to 'id' values. And that's problematic, because we have no way of knowing which of the 'nominations' rows a given ITO value was supposed to join to.
Does it matter? Or can it just be any of the X rows that have that same ITO?
-- hugh
A CDD is just like a database join - the 'value' (FK, foreign key) you use MUST be unique, so there's only one row in the joined table with that FK. So that the table join between the two tables only yields one row ... "LEFT JOIN othertable.pk = thistable.fk" ... so that relationship can only ever be a single row.
So this means that in list view, when we run the query, instead of selecting the 10 (or whatever) rows being displayed, the LEFT JOIN turns that into 100 or 500 or 1000 (depending how many duplicates of the FK are in the joined table). And because you have multiple joins, that effect gets compounded, into (probably) 10's of thousands of rows being selected ... which we then have to reduce down. And the effect on memory and processing is geometric. So with (say) 50 rows in your main table, you might not notice, but at 500, the effect is huge.
The only way to fix it is to use the 'id' as the value for the CDD. But that means modifying all your existing data, to convert existing 'ITO' value to 'id' values. And that's problematic, because we have no way of knowing which of the 'nominations' rows a given ITO value was supposed to join to.
Does it matter? Or can it just be any of the X rows that have that same ITO?
-- hugh