[jboss-jira] [JBoss JIRA] Commented: (JBAS-3644) ejbqltosql92compiler creates LEFT OUTER JOIN when INNER JOIN would be faster

ejbqltosql92compiler creates LEFT OUTER JOIN when INNER JOIN would be faster ---------------------------------------------------------------------------- Key: JBAS-3644 URL: https://jira.jboss.org/jira/browse/JBAS-3644 Project: JBoss Application Server Issue Type: Feature Request Security Level: Public(Everyone can see) Components: CMP service Affects Versions: JBossAS-4.0.3 SP1, JBossAS-5.0.0.Beta4 Environment: JBoss 4.0.3SP1, Sybase 12.5 (and Sybase 15.0) Reporter: anajavi Assignee: Alexey Loubyansky Attachments: ASTPath.java, EJBQLToSQL92Compiler.java Ejbqltosql92compiler creates LEFT OUTER JOIN's when INNER JOIN could be used. This leads to bad performance, because database server does table scan instead of using index. Running INNER JOIN instead of LEFT OUTER JOIN gives 10x better performance (0,6s/query vs. 0,06s/query). I submitted this as a feature request because it is about performance, not bug. However, this bad performance prevents us from using cmp2.x jdbc2 pm, which we would seriously need. I tried to go through ejbqltosql92compiler's source, but could not figure out a clean way to make it use INNER JOIN when joining is caused by a path in WHERE-clause (not containing OR). I also tried tweaking RDBMS optimizer and upgrading to newer version, but it did not help. Example: Original EJBQL: SELECT OBJECT(o) FROM Rooli AS o WHERE o.toimipaikka.yritys.id=?1 AND o.rooli.nimi=?2 Generated SQL: SELECT t0_o.id, t0_o.sahkoposti, t0_o.oletusrooli, t0_o.elinkaaren_pvm, t0_o.elinkaaren_tila, t0_o.fk_henkilo, t0_o.fk_toimipaikka, t0_o.fk_kooditus_rooli, t0_o.meta_fk_creator, t0_o.fk_osoite FROM rooli t0_o LEFT OUTER JOIN kooditus_arvo t2_o_rooli ON t0_o.fk_kooditus_rooli=t2_o_rooli.id LEFT OUTER JOIN toimipaikka t3_o_toimipaikka ON t0_o.fk_toimipaikka=t3_o_toimipaikka.id LEFT OUTER JOIN yritys t1_o_toimipaikka_yritys ON t3_o_toimipaikka.fk_yritys=t1_o_toimipaikka_yritys.id WHERE t1_o_toimipaikka_yritys.id = 42447 AND t2_o_rooli.nimi = 'aaa' 10x faster SQL: SELECT t0_o.id, t0_o.sahkoposti, t0_o.oletusrooli, t0_o.elinkaaren_pvm, t0_o.elinkaaren_tila, t0_o.fk_henkilo, t0_o.fk_toimipaikka, t0_o.fk_kooditus_rooli, t0_o.meta_fk_creator, t0_o.fk_osoite FROM rooli t0_o INNER JOIN kooditus_arvo t2_o_rooli ON t0_o.fk_kooditus_rooli=t2_o_rooli.id INNER JOIN toimipaikka t3_o_toimipaikka ON t0_o.fk_toimipaikka=t3_o_toimipaikka.id INNER JOIN yritys t1_o_toimipaikka_yritys ON t3_o_toimipaikka.fk_yritys=t1_o_toimipaikka_yritys.id WHERE t1_o_toimipaikka_yritys.id = 42447 AND t2_o_rooli.nimi = 'aaa' Thank you.