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

[anajavi (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBAS-3644?page=comments#action_12343765 ] 
            
anajavi commented on JBAS-3644:
-------------------------------

Modified version of compiler works as follows:
1. visiting ASTWhere node sets wherePath=true in all ASTWhere's child paths.
2. visiting ASTOr, ASTNullComparison or ASTIsEmpty causes their child paths to have forceLeftJoin=true
3. When visiting ASTPath compiler checks if wherePath is true and adds join based on forceLeftJoin field. If wherePath is false compiler works as before.
4. If some path has forceLeftJoin set, it cannot be reset to innerjoin

Otherwise the compiler works as before.

Is there something i'm missing or can this be added to default JBoss?

This really boosted performance for us.


> 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: 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.

-- 
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