Hi Mark, SQL translator in 4.2 is highly customizable via SQLTreeProcessor (returned from DbAdapter). So can you rearrange your joins with a custom adapter / tree processor.
Before you do that, please confirm whether this is still a problem with a newer version of Cayenne. Either with 4.2.RC2 (released) or the 4.2 final (upcoming, temporarily available at [1], and soon to be promoted to Central. Andrus [1] https://repository.apache.org/content/repositories/orgapachecayenne-1052/ > On May 21, 2023, at 1:19 AM, Mark Stobbe <markstobb...@gmail.com> wrote: > > Hi all, > > We are running into an issue with the specific join ordering that Cayenne ( > 4.2.M3) does. We are using a ColumnSelect to select multiple columns across > different tables with a simple where statement. > > SELECT * FROM my_table t0 LEFT JOIN my_detail1 t1 ON t0.fk_one = t1.id LEFT > JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN my_detail3 t3 ON > t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four = t4.id > ... JOIN my_children t12 ON t0.id = t12.fk_parent WHERE ( ((t12.c_from >= > '2023-05-16 10:00:00' AND t12.c_from <= '2023-05-17 10:00:00') OR (t12.c_to >> = '2023-05-16 10:00:00' AND t12.c_to <= '2023-05-17 10:00:00')) > ) > ... > LIMIT 25 > > This query takes approx. 30 seconds > where as if I manually change the ordering of the joins to: > > SELECT * FROM my_table t0 > JOIN my_children t12 ON t0.id = t12.fk_parent LEFT JOIN my_detail1 t1 ON > t0.fk_one = t1.id LEFT JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN > my_detail3 t3 ON t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four > = t4.id > ... WHERE ( ((t12.c_from >= '2023-05-16 10:00:00' AND t12.c_from <= > '2023-05-17 10:00:00') OR (t12.c_to >= '2023-05-16 10:00:00' AND t12.c_to > <= '2023-05-17 10:00:00')) > ) > ... > LIMIT 25 > > Then the query returns in a few milliseconds. > Can we somehow indicate the join ordering or at least prefer joins from the > where to go before the joins needed for the select ? > > Mark