Enterprise Java Development@TOPIC@

JPA Tuning

Performance Topics Related to JPA and SQL

Revision: v2013-11-14

2014-03-07 00:12 EST

Copyright © 2014 jim stafford (jcstaff@apl.jhu.edu)

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.

Topics
1. SQL Tuning
1.1. Reasons for Inefficient SQL Performance
1.2. Execution Plan
1.3. Diagnostic Tools
1.3.1. Client/DAO Result
1.3.2. EXPLAIN PLAN
1.3.3. AUTOTRACE
1.3.4. Display Cursor Execution Plan within V$PLAN
1.4. Summary
2. Example Domain Model: Movies
2.1. Class Model
2.2. Database Schema
2.3. Database Size
2.4. Prepare DB Between Tests
3. Table Access
3.1. Full Table Scan
3.1.1. Full Table Scan: Unconstrained Access
3.1.2. Full Table Scan: Using Where (without Index)
3.1.3. RowId Scan: Using Where (with Index)
3.1.4. Full Table Scan: Invalidating Index using Function applied to Row Column
3.1.5. RowId Scan: Using Index with Function applied to Row Column
3.1.6. Full Table Scan: Invalidating Index by using Leading Wildcards
3.2. Order By
3.2.1. Order By using Sort
3.2.2. Order By using Index
3.2.3. Order By using Index DESC
3.2.4. Order By using Reverse Index DESC
3.3. Summary
4. Indexes
4.1. Index Range Scan
4.2. Unique Index Scan
4.3. Composite Index
4.3.1. Query Parts
4.3.2. First Term Indexed
4.3.3. First and Second Term Indexed (using Composite Index)
4.4. Index Fast Full Scan (with Composite Index)
4.4.1. Query Parts
4.4.2. Option: Use Range Scan and RowId Access
4.4.3. Option: Use Range Scan Alone with Composite Index
4.4.4. Option: Fast Full Scan
4.5. Summary
5. Joins
5.1. Foreign Keys
5.1.1. Query Parts
5.1.2. No Indexes
5.1.3. Perform Query with Support for Foreign Key Index
5.1.4. Foreign Key and Where Columns Indexed
5.1.5. Foreign Key, Where, and Join Columns Indexed
5.2. Join Types
5.2.1. Nested Loop Join
5.2.2. Hash Join
5.2.3. Sort Merge Join
5.3. Summary
6. JPA
6.1. Lazy and Eager Fetching
6.1.1. Get Parent
6.1.2. Get Parent and Children
6.2. Obtaining Instance Counts
6.2.1. Query Parts
6.2.2. Collection Size in DAO from Relation
6.2.3. Row Count in DAO from Query
6.2.4. Row Count in DB using Count() Query
6.2.5. Row Count in DB using Count Query without JOIN
6.3. Query Loops
6.3.1. Query Parts
6.3.2. Query Loops in DAO
6.3.3. Query Loops using DB Subquery
6.4. Paging
6.4.1. Query Parts
6.4.2. Paging within DAO
6.4.3. Paging within DB
6.5. Summary
7. H2 Execution Plans
7.1. Column Index
7.2. Summary
8. JPA/SQL Tuning Summary
8.1. Other Topics