| All problems are now solved.
The second problem was just a wrong join which I had to remove.
/Manuel On 14.08.2012, at 09:02, Manuel Blechschmidt wrote: Hello Andrei, I now updated eclipselink to 2.4.0 and the deletes are a lot faster then before. Before: ~50 seconds Now: ~1,2 seconds Do you have a wishlist somewhere so I can buy you a small reward for helping me? Otherwise I can also donate some dollars to the eclipse foundation. I am currently having some other problems with eclipselink but I think I can solve some for myself e.g.: SELECT Rating r FROM Rating r WHERE TYPE(r.thing) IN :types Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [SELECT DISTINCT r.thing.id FROM Rating r WHERE r.state IN :ratingStates AND TYPE(r.thing) IN :thingTypes AND r.context IN :contexts AND r.person.id = :personId]. [81, 88] The encapsulated _expression_ is not a valid _expression_. org.glassfish.deployment.common.DeploymentException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.EntityManagerSetupException Exception Description: Deployment of PersistenceUnit [semRecSys] failed. Close all factories for this PersistenceUnit. Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [SELECT DISTINCT r.thing.id FROM Rating r WHERE r.state IN :ratingStates AND TYPE(r.thing) IN :thingTypes AND r.context IN :contexts AND r.person.id = :personId]. [81, 88] The encapsulated _expression_ is not a valid _expression_. Changing it to the following: SELECT Rating r FROM Rating r LEFT JOIN Thing t WHERE TYPE(t) IN :types Produces a NullPointerException in HermeParser. I just removed this code from my application because it isn't used anymore. Further I currently also have the following problem: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [select COUNT(distinct entity) from Organization as entity left join entity.label as label where entity.label like :label]. [68, 80] The collection-valued path 'entity.label' But I would expect I will be able to solve it. Thanks a lot Manuel On 13.08.2012, at 19:51, Andrei Ilitchev wrote: Eclipselink 2.4 uses USING with MySQL:
em.createQuery("DELETE FROM Employee e WHERE e.firstName = '"+firstName+"'").executeUpdate();
generates:
[EL Fine]: sql: 2012-08-13 13:26:08.414--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--CREATE TEMPORARY TABLE IF NOT EXISTS TL_CMP3_EMPLOYEE (EMP_ID INTEGER NOT NULL, F_NAME VARCHAR(255), GENDER VARCHAR(255), L_NAME VARCHAR(255), PAY_SCALE VARCHAR(255), ROOM_NUM INTEGER, STATUS INTEGER, VERSION INTEGER, START_TIME TIME, END_TIME TIME, START_OVERTIME TIME, END_OVERTIME TIME, FORMER_COMPANY VARCHAR(255), FORMER_END_DATE DATE, FORMER_START_DATE DATE, END_DATE DATE, START_DATE DATE, ADDR_ID INTEGER, DEPT_ID INTEGER, MANAGER_EMP_ID INTEGER, HUGE_PROJ_ID INTEGER, PRIMARY KEY (EMP_ID))
[EL Finest]: sql: 2012-08-13 13:26:08.414--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--Table tl_cmp3_employee already exists:42S01 - null
[EL Fine]: sql: 2012-08-13 13:26:08.414--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--INSERT INTO TL_CMP3_EMPLOYEE (EMP_ID) SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = ?) AND (t1.EMP_ID = t0.EMP_ID))
bind => [testUpdateUsingTempStorage]
[EL Fine]: sql: 2012-08-13 13:26:08.429--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM CMP3_EMP_PROJ USING CMP3_EMP_PROJ, TL_CMP3_EMPLOYEE WHERE CMP3_EMP_PROJ.EMPLOYEES_EMP_ID = TL_CMP3_EMPLOYEE.EMP_ID
[EL Fine]: sql: 2012-08-13 13:26:08.429--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM Employee_WORKWEEK USING Employee_WORKWEEK, TL_CMP3_EMPLOYEE WHERE Employee_WORKWEEK.EMP_ID = TL_CMP3_EMPLOYEE.EMP_ID
[EL Fine]: sql: 2012-08-13 13:26:08.429--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM CMP3_RESPONS USING CMP3_RESPONS, TL_CMP3_EMPLOYEE WHERE CMP3_RESPONS.EMP_ID = TL_CMP3_EMPLOYEE.EMP_ID
[EL Fine]: sql: 2012-08-13 13:26:08.429--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM CMP3_SALARY USING CMP3_SALARY, TL_CMP3_EMPLOYEE WHERE CMP3_SALARY.EMP_ID = TL_CMP3_EMPLOYEE.EMP_ID
[EL Fine]: sql: 2012-08-13 13:26:08.445--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM CMP3_EMPLOYEE USING CMP3_EMPLOYEE, TL_CMP3_EMPLOYEE WHERE CMP3_EMPLOYEE.EMP_ID = TL_CMP3_EMPLOYEE.EMP_ID
[EL Fine]: sql: 2012-08-13 13:26:08.445--ClientSession(1708945)--Connection(26485366)--Thread(Thread[Thread-5,6,main])--DELETE FROM TL_CMP3_EMPLOYEE
The method appeared in MySQLPlatform class for a long time but unfortunately until 2.4 had an error in its signature and was ignored.
All it takes to fix it in 2.3 is to change in MySQLPlatform
from:
public void writeDeleteFromTargetTableUsingTempTableSql(Writer writer, DatabaseTable table, DatabaseTable targetTable,
Collection pkFields, Collection targetPkFields) throws IOException
to:
public void writeDeleteFromTargetTableUsingTempTableSql(Writer writer, DatabaseTable table, DatabaseTable targetTable,
Collection pkFields, Collection targetPkFields, DatasourcePlatform platform) throws IOException
The same could be achieved by defining a custom MySQLPlatform that fixes the method's signature:
public class MyMySQLPlatform extends MySQLPlatform {
@Override
public void writeDeleteFromTargetTableUsingTempTableSql(Writer writer, DatabaseTable table, DatabaseTable targetTable,
Collection pkFields, Collection targetPkFields, DatasourcePlatform platform) throws IOException {
writeDeleteFromTargetTableUsingTempTableSql(writer, table, targetTable, pkFields, targetPkFields);
}
}
On 8/12/2012 5:30 PM, Manuel Blechschmidt wrote:
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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
-- Manuel Blechschmidt M.Sc. IT Systems Engineering Dortustr. 57 14467 Potsdam Mobil: 0173/6322621 Twitter: http://twitter.com/Manuel_B_______________________________________________ eclipselink-users mailing list eclipselink-users@xxxxxxxxxxxhttps://dev.eclipse.org/mailman/listinfo/eclipselink-users
M.Sc. IT Systems EngineeringDortustr. 57 Mobil: 0173/6322621
|
