EclipseLink JPA Black Belt Course

1. EclipseLink JPABlack Belt Course Section 10: Performance Tuning <PRESENTER>

2. EclipseLink JPA: Tuning • Minimize database queries • Effective modeling • Query optimizations • Leverage Cache • Cache Configuration per class • Cache Coordination

3. Lazy / Just in Time Reading • Use of proxy to defer reading until required • Very valuable performance feature • Several Implementation Options • Explicit proxy • Dynamic proxy (java.lang.reflect.Proxy) • Development time class enhancement (source or byte codes) • Weaving Customer ValueHolder Address PhoneNumber List

4. ORM Mapping: Inheritance • Java • Used to extend (share) common state & behavior • Relational • Shared data can be normalized into common table • Adds additional unique constraint within common table

5. Parent id : int name : String ChildOne details1 : String ChildTwo details2 : String Inheritance: Object • Only map concrete subclasses • PRO: No joins or table contention between types • CON: No heterogeneous query results CHILD_ONE ID : NUMBER NAME : VARCHAR2(10) DETAILS1 : VARCHAR2(100) <<PK>> CHILD_ONE_PK : ID CHILD_TWO ID : NUMBER NAME : VARCHAR2(10) DETAILS2 : VARCHAR2(100) <<PK>> CHILD_TWO_PK : ID

6. Inheritance: Object & Relational • Root class has its own table • PRO: Heterogeneous query results possible • CON: Additional queries or joins required PARENT ID : NUMBER NAME : VARCHAR2(10) TYPE : CHAR(1) <<PK>> PARENT_PK : ID 1 1 * * CHILD_ONE ID : NUMBER DETAILS1 : VARCHAR2(100) <<PK>> CHILD_ONE_PK : ID CHILD_TWO ID : NUMBER DETAILS1 : VARCHAR2(100) <<PK>> CHILD_TWO_PK : ID

7. Inheritance: Optimized • Root class has its own table + subclass data • PRO: • Heterogeneous query results possible • No extra queries or joins for subclasses • CON: Additional table size, unused columns • Example: Same object mode, one shared table PARENT ID : NUMBER NAME : VARCHAR2(10) TYPE : CHAR(1) DETAILS1 : VARCHAR2(100) DETAILS2 : VARCHAR2(100) <<PK>> PARENT_PK : ID

8. Flexibility vs. Performance • Avoid 1:1 relationships without FKs • Flexible ‘typed’ relationships may make OO model more flexible • Reduces ability to join across relationship for SQL optimizations • Introduce flexibility ONLY where required • “Excessive flexibility will have performance costs”

9. Concurrency: Locking • Proper locking semantics is a must • Pessimistic: SELECT … FOR UPDATE [NO WAIT] • Optimistic • Version column (Numeric or Timestamp) • All or some data columns • Cascaded

10. Locking Recommendations • Use optimistic locking • Reads can avoid unnecessary refresh by comparing lock values • Minimize pessimistic locking • Reduce database resource contention with longer transactions • Use when retry is more costly then locking • Typically used in data processing or non-user interactive scenarios

11. Querying: Reading • Critical element of ORM tuning • Most applications have more reading then writing • GOALS • Reducing the amount of SELECT statements • Optimize the value of each SELECT statement • RECOMMENDED SOLUTIONS • Just in time reading • Result caching – object or result container • Optimized SQL generation • Data projections versus full object loading

12. Customer id : int address : Address phoneNumbers : List PhoneNumber customer : Customer number : String type : String PurchaseOrder id : int customer : Customer Example Object Model 1 1 * 1 Address id : int street : String city : String state : String postalCode : String country : String

13. Query Example • Find all active PO’s with a customer in San Francisco and display customer details ReadAllQuery raq = new ReadAllQuery(PurchaseOrder.class); ExpressionBuilder eb = raq.getExpressionBuilder(); Expression custExp = eb.get("customer"); Expression addrExp = custExp.get(“address”); Expression sfoExp = addrExp.get("city").equal("SFO"); Expression isActiveExp = eb.get("status").equal("ACTIVE"); raq.setSelectionCriteria(sfoExp.and(isActiveExp));

14. Query Example: Initial SQL SELECT PO.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ {Returns N Purchase Orders} SELECT * FROM CUST WHERE CUST.ID = 1 … {N} SELECT * FROM ADDR WHERE ADDR.ID = 100 … {N} SELECT * FROM PHONE WHERE PHONE.CUST_ID = 1 … {N} RESULT: 3N+1 queries (100 PO’s = 301 SQL) • “N+1 Query – explosion of SQL”

15. Join Reading 1:1’s • Join Reading • Read in rows that can be joined together in a single SELECT ReadAllQuery raq = new ReadAllQuery(PurchaseOrder.class); ExpressionBuilder eb = raq.getExpressionBuilder(); Expression custExp = eb.get("customer"); Expression addrExp = custExp.get(“address”); Expression sfoExp = addrExp.get("city").equal("SFO"); Expression isActiveExp = eb.get("status").equal("ACTIVE"); raq.setSelectionCriteria(sfoExp.and(isActiveExp)); raq.addJoinedAttribute(custExp); raq.addJoinedAttribute(addrExp);

16. Join Reading 1:1’s • SQL: SELECT PO.*,CUST.*,ADDR.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ {Returns N Purchase Orders with Customers & Addresses} SELECT * FROM PHONE WHERE PHONE.CUST_ID = 1 …{N calls} RESULT: N+1 queries (100 PO’s = 101 SQL)

17. Join Reading All Relationships • Join Reading • Read in rows that can be joined together in a single SELECT ReadAllQuery raq = new ReadAllQuery(PurchaseOrder.class); ExpressionBuilder eb = raq.getExpressionBuilder(); Expression custExp = eb.get("customer"); Expression addrExp = custExp.get(“address”); Expression sfoExp = addrExp.get("city").equal("SFO"); Expression isActiveExp = eb.get("status").equal("ACTIVE"); raq.setSelectionCriteria(sfoExp.and(isActiveExp)); raq.addJoinedAttribute(custExp); raq.addJoinedAttribute(addrExp); raq.addJoinedAttribute(custExp.anyOf(“phoneNumbers”));

18. Join Reading All Relationships • SQL: SELECT PO.*,CUST.*,ADDR.*, PHONE.* FROM PO, CUST, ADDR, PHONE WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ AND PHONE.CUST_ID = CUST.ID {Returns N Purchase Orders with Customers, Addresses, and PhoneNumbers} • RESULT: 1 query (100 PO’s = 1 SQL)

19. Batch Reading • Multiple queries using optimized joining and original selection criteria ReadAllQuery raq = new ReadAllQuery(PurchaseOrder.class); ExpressionBuilder eb = raq.getExpressionBuilder(); Expression custExp = eb.get("customer"); Expression addrExp = custExp.get(“address”); Expression sfoExp = addrExp.get("city").equal("SFO"); Expression isActiveExp = eb.get("status").equal("ACTIVE"); raq.setSelectionCriteria(sfoExp.and(isActiveExp)); raq.addBatchReadAttribute(custExp); raq.addBatchReadAttribute(addrExp); raq.addBatchReadAttribute(custExp.get(“phoneNumbers”));

20. Batch Reading • Use SELECT per child with join to original clause SELECT PO.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ SELECT CUST.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ SELECT ADDR.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ SELECT PHONE.* FROM PHONE, PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ AND PHONE.CUST_ID = CUST.ID • RESULT: 4 queries (100 PO’s = 4 SQL)

21. Combining Joining and Batch Reading ReadAllQuery raq = new ReadAllQuery(PurchaseOrder.class); ExpressionBuilder eb = raq.getExpressionBuilder(); Expression custExp = eb.get("customer"); Expression addrExp = custExp.get(“address”); Expression sfoExp = addrExp.get("city").equal("SFO"); Expression isActiveExp = eb.get("status").equal("ACTIVE"); raq.setSelectionCriteria(sfoExp.and(isActiveExp)); raq.addJoinedAttribute(custExp); raq.addJoinedAttribute(addrExp); raq.addBatchReadAttribute(custExp.get(“phoneNumbers”));

22. Batch and Join Reading • Use SELECT per child with join to original clause SELECT PO.*,CUST.*,ADDR.* FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ {Returns N Purchase Orders with Customers & Addresses} SELECT PHONE.* FROM PHONE, PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’ AND PHONE.CUST_ID = CUST.ID RESULT: 2 queries (100 PO’s = 2 SQL)

23. Query Optimization OptionsReading 100 PO’s with relationships # SQL Time Default 301 200 ms Join 1:1’s 101 150 ms Join All 1 60 ms Batch All 4 40 ms Batch & Join 2 20 ms Source: Simple test environment

24. Queries: Minimize Object Reading • Only read the entire object when required • When modifying the object • When caching will assist concurrent clients • All fields of the object are required • Consider projections versus object queries

25. Query Optimization: Projection • Specify the required fields in terms of the object model • Use relationships to indicate joins • Use aggregate functions • SUM, MIN, MAX, COUNT, AVERAGE • Produce easy to render results

26. Query: Projection Example • JP QL: SELECT new PODetails(po.id, po.num, po.amount, c.lastName, a.city) FROM PurchaseOrder po JOIN po.customer c, JOIN po.customer.address a WHERE po.status = 'ACTIVE‘ AND a.city = 'SFO‘ • SQL: SELECT PO.ID, PO.NUM, PO.AMOUNT, CUST.L_NAME, ADDR.CITY FROM PO, CUST, ADDR WHERE PO.STATUS = ‘ACTIVE’ AND PO.CUST_ID = CUST.ID AND CUST.ADDR_ID = ADDR.ID AND ADDR.CITY = ‘SFO’

27. Performance and Tuning • TopLink focuses on performance and scalability • Highly configurable and tunable • Guiding principle: minimize and optimize database calls • Enable application specific tuning - No two applications are the same • Flexibility of TopLink allows efficient business models and relational schemas to be used • Leverages underlying performance tuning features • Java, JDBC and the underlying database technology • TopLink EJB CMP major contributor to Oracle’s World Record SpecJ benchmark results • Performance enhancements leveraged by POJO-ORM and JPA

28. Performance and Tuning Options • “Just in Time” reading • Automatic change detection • Caching policies and sizes • Parameterized SQL (binding) • Pre-allocation of sequence numbers • Cache Coordination • Optimistic, Pessimistic locking • Joining object retrieval optimization • In memory querying • Dynamic queries • Optimized Change Tracking • Minimal Writes, Updates • Batch Reading, Writing • SQL ordering • Transformation support • Existence checks • Stored procedures • Statement Caching • Scrolling cursors • Projection Queries • Partial Attribute Queries • Bulk Update Queries AND MUCH MORE!