Enterprise Java Development@TOPIC@

Part XIII. JPA Tuning

Performance Topics Related to JPA and SQL

Copyright © 2015 jim stafford (jim.stafford@jhu.edu)

2015-11-18 02:21 EST

Abstract

This presentation provides information for JPA/SQL developers to better understand how database constructs and database access decisions can impact application performance. It provides a brief discussion of tools that can be used and how to review an execution plan. Relative comparisons between approaches are provided to help show the costs and benefits of different approaches.

Table of Contents

Topics
76. SQL Tuning
76.1. Reasons for Inefficient SQL Performance
76.2. Execution Plan
76.3. Diagnostic Tools
76.3.1. Client/DAO Result
76.3.2. EXPLAIN PLAN
76.3.3. AUTOTRACE
76.3.4. Display Cursor Execution Plan within V$PLAN
76.4. Summary
77. Example Domain Model: Movies
77.1. Class Model
77.2. Database Schema
77.3. Database Size
77.4. Prepare DB Between Tests
78. Table Access
78.1. Full Table Scan
78.1.1. Full Table Scan: Unconstrained Access
78.1.2. Full Table Scan: Using Where (without Index)
78.1.3. RowId Scan: Using Where (with Index)
78.1.4. Full Table Scan: Invalidating Index using Function applied to Row Column
78.1.5. RowId Scan: Using Index with Function applied to Row Column
78.1.6. Full Table Scan: Invalidating Index by using Leading Wildcards
78.2. Order By
78.2.1. Order By using Sort
78.2.2. Order By using Index
78.2.3. Order By using Index DESC
78.2.4. Order By using Reverse Index DESC
78.3. Summary
79. Indexes
79.1. Index Range Scan
79.2. Unique Index Scan
79.3. Composite Index
79.3.1. Query Parts
79.3.2. First Term Indexed
79.3.3. First and Second Term Indexed (using Composite Index)
79.4. Index Fast Full Scan (with Composite Index)
79.4.1. Query Parts
79.4.2. Option: Use Range Scan and RowId Access
79.4.3. Option: Use Range Scan Alone with Composite Index
79.4.4. Option: Fast Full Scan
79.5. Summary
80. Joins
80.1. Foreign Keys
80.1.1. Query Parts
80.1.2. No Indexes
80.1.3. Perform Query with Support for Foreign Key Index
80.1.4. Foreign Key and Where Columns Indexed
80.1.5. Foreign Key, Where, and Join Columns Indexed
80.2. Join Types
80.2.1. Nested Loop Join
80.2.2. Hash Join
80.2.3. Sort Merge Join
80.3. Summary
81. JPA
81.1. Lazy and Eager Fetching
81.1.1. Get Parent
81.1.2. Get Parent and Children
81.2. Obtaining Instance Counts
81.2.1. Query Parts
81.2.2. Collection Size in DAO from Relation
81.2.3. Row Count in DAO from Query
81.2.4. Row Count in DB using Count() Query
81.2.5. Row Count in DB using Count Query without JOIN
81.3. Query Loops
81.3.1. Query Parts
81.3.2. Query Loops in DAO
81.3.3. Query Loops using DB Subquery
81.4. Paging
81.4.1. Query Parts
81.4.2. Paging within DAO
81.4.3. Paging within DB
81.5. Summary
82. H2 Execution Plans
82.1. Column Index
82.2. Summary
83. JPA/SQL Tuning Summary
83.1. Other Topics