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
