SQL Performance Explained Everything Developers Need to Know about SQL Performance by Winand Markus

Author:Winand, Markus
Language: eng
Format: epub
Publisher: Markus Winand
Published: 2023-10-21T00:00:00+00:00

Warning

JPA and Hibernate return the employees for each sale.

That means that an employee with 30 sales will appear 30 times. Although it is very disturbing, it is the specified behavior (EJB 3.0 persistency, paragraph 4.4.5.3 “Fetch Joins”). You can either manually de-duplicate the parent relation or use the function distinct() as shown in the example.

CriteriaBuilder qb = em.getCriteriaBuilder();

CriteriaQuery<Employees> q = qb.createQuery(Employees.class); Root<Employees> r = q.from(Employees.class);

q.where(queryBuilder.like(

queryBuilder.upper(r.get(Employees_.lastName)), "WIN%")

);

r.fetch("sales", JoinType.LEFT);

// needed to avoid duplication of Employee records query.distinct(true);

List<Employees> emp = em.createQuery(query).getResultList();

Hibernate 3.6.0 generates the following SQL statement:

select distinct

employees0_.subsidiary_id as subsidiary1_0_0_

, employees0_.employee_id as employee2_0_0_

-- MORE COLUMNS

, sales1_.sale_id as sale1_0__

from employees employees0_

left outer join sales sales1_

on employees0_.subsidiary_id=sales1_.subsidiary_id and employees0_.employee_id=sales1_.employee_id where upper(employees0_.last_name) like ?

The query has the expected left join but also an unnecessary distinct keyword. Unfortunately, JPA does not provide separate API calls to filter duplicated parent entries without de-duplicating the child records as well. The distinctkeyword in the SQL query is alarming because most databases will actually filter duplicate records. Only a few databases recognize that the primary keys guarantees uniqueness in that case anyway.

The native Hibernate API solves the problem on the client side using a result set transformer:

Criteria c = session.createCriteria(Employees.class); c.add(Restrictions.ilike("lastName", 'Win%')); c.setFetchMode("sales", FetchMode.JOIN);

c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Employees> result = c.list();

It generates the following query:

select this_.subsidiary_id as subsidiary1_0_1_ , this_.employee_id as employee2_0_1_

-- MORE this_ columns on employees

, sales2_.sale_id as sale1_3_

-- MORE sales2_ columns on sales

from employees this_

left outer join sales sales2_

on this_.subsidiary_id=sales2_.subsidiary_id and this_.employee_id=sales2_.employee_id where lower(this_.last_name) like ?

This method produces straight SQL without unintended clauses. Note that Hibernate uses lower()for case-insensitive queries— an important detail for function-based indexing.

Perl

The following example uses Perl’s DBIx::Class framework:

my @employees =

$schema->resultset('Employees')

->search({ 'UPPER(last_name)' => {-like => 'WIN%'} , {prefetch => ['sales']}

});

DBIx::Class 0.08192 generates the following SQL statement:

SELECT me.employee_id, me.subsidiary_id, me.last_name

-- MORE COLUMNS

FROM employees me

LEFT JOIN sales sales

ON (sales.employee_id = me.employee_id AND sales.subsidiary_id = me.subsidiary_id) WHERE ( UPPER(last_name) LIKE ? )

ORDER BY sales.employee_id, sales.subsidiary_id

Note the order byclause — it was not requested by the application. The database has to sort the result set accordingly, and that might take a while.

PHP The following example uses PHP’s Doctrine framework:

$qb = $em->createQueryBuilder();

$qb->select('e,s')

->from('Employees', 'e')

->leftJoin('e.sales', 's')

->where("upper(e.last_name) like :last_name")

->setParameter('last_name', 'WIN%');

$r = $qb->getQuery()->getResult();

Doctrine 2.0.5 generates the following SQL statement:

SELECT e0_.employee_id AS employee_id0

-- MORE COLUMNS

FROM employees e0_

LEFT JOIN sales s1_

ON e0_.subsidiary_id = s1_.subsidiary_id AND e0_.employee_id = s1_.employee_id WHERE UPPER(e0_.last_name) LIKE ?

The execution plan shows the NESTED LOOPS OUTER operation:

--------------------------------------------------------------|Id |Operation | Name | Rows | Cost |

--------------------------------------------------------------| 0 |SELECT STATEMENT | | 822 | 38 | | 1 | NESTED LOOPS OUTER | | 822 | 38 | | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 4 | |*3 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | | | 4 | TABLE ACCESS BY INDEX ROWID| SALES | 821 | 34 | |*5 | INDEX RANGE SCAN | SALES_EMP | 31 | |

--------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------

3 - access(UPPER("LAST_NAME") LIKE 'WIN%')

filter(UPPER("LAST_NAME") LIKE 'WIN%')

5 - access("E0_"."SUBSIDIARY_ID"="S1_"."SUBSIDIARY_ID"(+) AND "E0_"."EMPLOYEE_ID" ="S1_"."EMPLOYEE_ID"(+))

The database retrieves the result from the EMPLOYEEStable via EMP_UP_NAME first and fetches the corresponding records from the SALEStable for each employee afterwards.

Tip

Get to know your ORM and take control of joins.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.