[eclipselink-users] Slow deletes when using eclipse link because of temp

[Manuel Blechschmidt <Manuel.Blechschmidt@xxxxxx>]

Hello everybody,
I am using Glassfish 3.1.2 which bundles eclipse link 2.3.1 or 2.3.2. We currently have an performance issue with delete queries.

We have about 80 entities and there are a lot of inheritance structures e.g. Thing -> Agent -> Person. We are using @Inheritance(strategy = InheritanceType.JOINED).

This works just fine. The big problem is that when I am deleting an object e.g. a Person then there are multiple rows to be deleted. The THING tables contains in our case about 1.000.000 entries and it is growing every day.

The big problem is the EXISTS query with the temporary table e.g.:
DELETE FROM THING WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = THING.ID);

The following query would a 1000 times faster:

DELETE FROM THING WHERE ID IN (
    SELECT ID FROM TL_THING
)

The problem is that MySQL is not optimizing the query correctly.

Here are some queries from our slow query log:
...
# Time: 120812 21:10:06
# User@Host: jpa[jpa] @  [10.240.242.255]
# Query_time: 19.850080  Lock_time: 0.016071 Rows_sent: 0  Rows_examined: 3526831
SET timestamp=1344805806;
DELETE FROM RECEIVER WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = RECEIVER.ID);
...
# Time: 120812 21:10:31
# User@Host: jpa[jpa] @  [10.240.242.255]
# Query_time: 24.302330  Lock_time: 0.000091 Rows_sent: 0  Rows_examined: 3530117
SET timestamp=1344805831;
DELETE FROM PARTICIPANT WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = PARTICIPANT.ID);
...
# Time: 120203 17:44:02
# User@Host: jpa[jpa] @  [10.50.6.150]
# Query_time: 11.923321  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 138425
SET timestamp=1328291042;
/*  */ DELETE FROM THING WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = THING.ID);
# Time: 120203 17:44:28
# User@Host: jpa[jpa] @  [10.50.6.150]
# Query_time: 12.086030  Lock_time: 0.000103 Rows_sent: 0  Rows_examined: 138419
SET timestamp=1328291068;
/*  */ DELETE FROM THING WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = THING.ID);
# Time: 120203 17:45:03
# User@Host: jpa[jpa] @  [10.50.6.150]
# Query_time: 13.179641  Lock_time: 0.000100 Rows_sent: 0  Rows_examined: 138413
SET timestamp=1328291103;
/*  */ DELETE FROM THING WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = THING.ID);
# Time: 120203 17:47:18
# User@Host: jpa[jpa] @  [10.50.6.150]
# Query_time: 14.438851  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 138377
SET timestamp=1328291238;
/*  */ DELETE FROM THING WHERE EXISTS(SELECT ID FROM TL_THING WHERE ID = THING.ID);
...

Here is a typical query that we are using.

Query queryObject = emanager.createQuery("DELETE FROM ViewedThing vt WHERE vt.thing.id = :id");
queryObject.setParameter("id", messageEvent.getId());
queryObject.setFlushMode(FlushModeType.COMMIT);
queryObject.executeUpdate();

Are there any recommendations what I can do to make these queries faster?

/Manuel

-- 
Manuel Blechschmidt
M.Sc. IT Systems Engineering
Dortustr. 57
14467 Potsdam
Mobil: 0173/6322621
Twitter: http://twitter.com/Manuel_B