I see. It’s a good question, but I would just stick with .andExp in that case. I prefer using that one anyway due to historical usage and also to keep things consistent if I have Expressions defined apart from queries.
On Tue, Aug 18, 2020 at 4:30 PM Hugi Thordarson <[email protected]> wrote: > Hi John, > > the grouping of the expressions (the locations of the parentheses) only > relate to the Cayenne expression. The main issue is the different SQL > generated by Cayenne 4.2 from those expressions due to the grouping. > > If you look at the SQL generated for (2), the last condition in the where > clause (that uses date_time) is applied with a "=" rather than "AND". It's > something I honestly just haven't seen or used before (and I'm embarrassed > not knowing how or why it works, reading up right now). But I assume it > means it first applies the first condition and then applies the second > condition to the result of that to return a final result. In my case, that > would make sense performance wise—I have indexes on "date_time" so if > date_time is not included in the first query, we're going to have a bad > time. > > What I don't get is why that "=" is generated instead of and "AND". If > there's an "AND" the index gets used, regardless of the order of the > expressions. > > - hugi > > > > > > On 18 Aug 2020, at 18:10, John Huss <[email protected]> wrote: > > > > I would tend to think the postgres query planner is smart enough to > handle > > the differences in the placement of parentheses if that is the only > > difference. But in any case, try adding "explain analyze" to the > beginning > > of the query and then compare the query plans between the fast version > and > > the slow version. You can post them here too. That would be the best > place > > to start I think. > > > > On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <[email protected]> > wrote: > > > >> Hi all, > >> I'm currently migrating a large project to Cayenne 4.2. Code wise the > >> transition was easy enough, apart from one thing. > >> > >> Consider the following two expressions. Both are generated from the same > >> three conditions. > >> > >> In the first case they're combined using exp1.andExp( exp2 ).andExp( > exp3 > >> ). > >> > >> In the second case, they're combined using: > >> > >> ObjectSelect > >> .query( SomeClass.class ) > >> .where( exp1 ) > >> .and( exp2 ) > >> .and( exp3 ) > >> > >> The two different methods yield: > >> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>) > >> and (key.number = "161-400") and (date > 2015-08-18) > >> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>) > >> and (key.number = "161-400")) and (date > 2015-08-18) > >> > >> Note the different nesting. In Cayenne 4.1 this was not a problem since > >> the two expressions generated more or less the same SQL. > >> > >> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different, > >> resulting in a large performance loss in the latter case (as in, queries > >> that previously took a few of milliseconds now take minutes). Here's the > >> SQL generated — note the comparison that happens with the last > (date_time) > >> condition in (2): > >> > >> SQL generated by 1: > >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" > "t1" > >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" = > >> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND ( > >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND ( > >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40, > >> 3->gl_number:'161-400', 4->date_time:2015-08-18] > >> > >> SQL generated by 2: > >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" > "t1" > >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" = > >> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND ( > >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = ( > >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40, > >> 3->gl_number:'161-400', 4->date_time:2015-08-18] > >> > >> Any ideas? > >> > >> I'd like to add the disclaimer that I have not been able to identify the > >> exact conditions that result in the generation of the slower SQL, but it > >> certainly does not happen in all cases (i.e. will only happen with some > >> combinations of conditions). > >> > >> But I'd be happy to try to create a reproducible standalone test-case if > >> required, just give me a shout. I just wanted to start out by throwing > this > >> out there. > >> > >> Cheers, > >> - hugi > >
