Serverside joined tables

RagnarGrootKoerkamp

We are using serverside processing to obtain data for table A, and we join data from table B to this.

Normally, one can repeat the where conditions from the main Editor instance on the Join instance, so that the join query only selects data for the rows that are present in the result set. When using server side processing, the where condition are only applied to the top level query, not to the join query. In our case, this results in a memory limit error, because the Join class obtains all data from the server, while it only uses/needs a couple of rows.

There are a couple of solutions I think of:
1. Only query the required rows, using WHERE main_primary_key IN ( .. )
2. Only query the required rows, using prepared statements. Iterate over the data, bind the id, and store the result of the query.
3. In the _ssp_query method, add the where conditions to the editor instance instead of directly to the query. Then, in Join->data(), apply all where conditions on the editor instance to the join query as well.

I don't know which of the first two options would be faster.
Personally, I prefer the first two options, because the third one has to search the database again, which (in our case) can be a costly operation. Also, the first two options could select from the joined table directly, instead of joining from the main table.

This optimization probably isn't very useful when serverside processing is not used, because then all rows are sent anyway (or where conditions could be manually applied to the join instance).

allan

Thanks for your thoughts on this. Your option 1 is the one I've been thinking of implementing and its on my list. I don't have a committed release version for it yet, due to a number of other things I also want to add in, but I think that would be a nice addition.

Regards,
Allan