Poor performance for JPQL delete
--------------------------------

                 Key: OPENJPA-2062
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2062
             Project: OpenJPA
          Issue Type: Bug
          Components: jpa
    Affects Versions: 2.0.1
         Environment: Derby 10.5.3
            Reporter: Moti Nisenson


If I have a simple entity as such

@Entity
public class MyClass {
   @Id
   Long id;
   
   @Column(name="xyz", nullable=false)
   Long field;
}

And I have a JPQL delete query: "delete from MyClass e where e.field=?1"
this gets translated into the following sql: DELETE from MyClass where id in (select distinct t0.id from MyClass t0 where (to.xyz = ?))

The extra select with distinct (rather than just having DELETE from MyClass where xyz=?) causes a big performance slowdown; on the order of 5-10x (with Derby 10.5.3).

I didn't bother testing for different annotations, but I would imaging it's the same for all of them.

Please fix this; it is very basic functionality and the SQL generated should be the straightforward, high performing version.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira