1 / 28

EclipseLink JPA Black Belt Course

EclipseLink JPA Black Belt Course. Section 10: Performance Tuning <PRESENTER>. EclipseLink JPA: Tuning. Minimize database queries Effective modeling Query optimizations Leverage Cache Cache Configuration per class Cache Coordination. Lazy / Just in Time Reading.

muriel
Download Presentation

EclipseLink JPA Black Belt Course

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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!

More Related