Home » Eclipse Projects » EclipseLink » Optimizing EclipseLink generated SQL(Questions about the cartesian products it often generates)
| Optimizing EclipseLink generated SQL [message #521938] |
Fri, 19 March 2010 07:50  |
Eclipse User |
|
|
|
Hello, everybody.
I've recently posted a question on stackoverflow.com, about the SQL generated by EclipseLink: here.
As it is still lacks an answer from the good folks over there, I thought I might ask it here as well.
At the project I'm currently working on, we're updating the software from Java EE 5 to 6, due to many reasons, but mostly because we need the CDI support for upcoming modules. The previous version used Hibernate as the persistence provider, but the domain layer was "free" of provider-specific annotations, and so it was reused with few modifications to the mappings.
However, the SQL generated by EclipseLink for our previous JPQL queries contains several cartesian plans, instead of (inner) joins, despite paths being available on the tables to perform joins and squeeze better performance.
I can post code and examples if you want, but I would simply like to know if it's possible to fine tune this behaviour through configuration, rather than using annotations or query hints.
On a short note, even when I add the query hints to join or batch-fetch associations, EclipseLink still applies cartesian plans on its SQL queries.
Thanks in advance for any comments you might have on this issue.
|
|
| | |
| Re: Optimizing EclipseLink generated SQL [message #522506 is a reply to message #521938] |
Mon, 22 March 2010 16:25  |
| Eclipse User |
|
|
|
Hello James,
I am already applying lazy pagination, lazy loading, and query batching on the queries. I am not interest in using native SQL - my main issue is with the predicate of queries.
As you've asked, here's an example. This is a query outputed by EclipseLink:
FINE: SELECT t0.ID, t0.type, t0.CREATION, t0.NAME, t0.TYPE, t0.jpa_version, t1.ID, t1.EMAIL FROM swa_assignable t0, swa_resource t1 WHERE ((t0.id = ?) AND ((t1.ID = t0.ID) AND (t0.type = ?)))
bind => [6, R]
This is given by a simple EntityManager.find() query. The entities Assignable and Resource are related by Inheritance - Assignable is parent of Resource, and this is mapped using a PK-FK in the Resource table.
I simply posted the simplest example I could find; this happens to almost every query I have. For example, I have a WorkOrder entity with several @ManyToOne relationships; whenever I issue parameterized queries that deal with these relationships, instead of the appropriate joins, EclipseLink does these equals comparisons on a very large WHERE clause.
So I was looking for something (configuration-wise) that could instruct EclipseLink to use joins instead. Does that exist? (although by your reaction, I believe it does not...)
Thanks for replying!
|
|
| | |
| Re: Optimizing EclipseLink generated SQL [message #523242 is a reply to message #523213] |
Thu, 25 March 2010 12:06 |
| Eclipse User |
|
|
|
Hi again James,
As promised, the SQL samples, as generated by EclipseLink:
/* select clause abbreviated for clarity */
SELECT t1.*, t0.*, t2.*, t3.*, t4.*, t5.*
FROM swa_work_orders t1
LEFT OUTER JOIN swa_network_element t5 ON (t5.ID = t1.ne_id),
swa_priorities t4,
swa_dispatch_project t3,
swa_work_order_detail t2,
swa_work_order_type t0 WHERE ((((t1.severity = 1)
AND (t1.STATUS = 1))
AND (t1.project_id = 10001))
AND ((((t0.ID = t1.TYPE_ID)
AND (t2.worder_id = t1.ID))
AND (t3.id = t1.project_id))
AND (t4.ID = t1.priority_id)))
ORDER BY t1.CREATION DESC
And what we think should be generated:
SELECT t1.*, t0.*, t2.*, t3.*, t4.*, t5.*
FROM swa_work_orders t1
LEFT OUTER JOIN swa_network_element t5 ON t5.ID = t1.ne_id
inner join swa_priorities t4 on t1.priority_id = t4.id
inner join swa_dispatch_project t3 on t1.project_id = t3.id
inner join swa_work_order_detail t2 on t1.id = t2.worder_id
inner join swa_work_order_type t0 on t1.type_id = t0.id
WHERE t1.severity = 1
AND t1.STATUS = 1
AND t1.project_id = 10001
ORDER BY t1.CREATION DESC
If you compare execution plans for both queries, you'll see that the second format perfoms better, *especially* under heavy load of data on the involved tables.
The JPQL I am using to generate such a query is dynamic, and is the following:
select w from WorkOrder w
inner join fetch w.type
inner join fetch w.details
inner join fetch w.project
inner join fetch w.priority
left join fetch w.networkElement
where w.status = :status and w.severity = :severity
The relationships above are @ManyTone (type, project, priority) and @OneToOne (details), and the query itself is optimized with the usage of inner join fetch, since I need these dependencies to display data.
I'll try to attach the execution plans from the MS-SQL management application to support my case 
Regards,
Rodrigo
|
|
| | | |
Goto Forum:
Current Time: Sun Jul 06 20:48:43 EDT 2025
Powered by FUDForum. Page generated in 0.06794 seconds |
Home