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

[anajavi (JIRA)]

     [ http://jira.jboss.com/jira/browse/JBAS-3644?page=all ]

anajavi updated JBAS-3644:
--------------------------

    Attachment: EJBQLToSQL92Compiler.java

Modified version of EJBQLToSQL92Compiler. This version tries to use INNER JOIN where possible. It needs also two boolean fields added to ASTPath:
   public boolean forceLeftJoin;
   public boolean wherePath;

> ejbqltosql92compiler creates LEFT OUTER JOIN when INNER JOIN would be faster
> ----------------------------------------------------------------------------
>
>                 Key: JBAS-3644
>                 URL: http://jira.jboss.com/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
>         Environment: JBoss 4.0.3SP1, Sybase 12.5 (and Sybase 15.0)
>            Reporter: anajavi
>         Assigned To: Alexey Loubyansky
>         Attachments: 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira