[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Slow deletes when using eclipse link because of temporary tables in mysql
|
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
