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

[anajavi (JIRA)]

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


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