Download - High Performance Jdbc
![Page 2: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/2.jpg)
Agenda
What I do Persistence Performance JDBC
Connection Query Result
Timing Statistics
![Page 3: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/3.jpg)
Shameless Self-Promotion Consultant & Coach (available) I talk business
Trained Accountant, MBA VP Product Marketing
I talk tech Started programming PL/I, SmallTalk,
Oracle5 Director in Oracle Apps Development Certified Oracle DBA, Sun Java Pr/Dev/Arch
![Page 4: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/4.jpg)
Compiere The Open Source Business Solution
More than 240.000 downloads Usually among Top 10 in SourceForce
Targeting Small-Medium Enterprise ASP Solution Accounting, Inventory, … Worldwide (Language, Law, …)
J2EE compliant Application Swing or HTML interface
![Page 5: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/5.jpg)
Agenda -- Persistence
What I do Persistence Performance JDBC
Connection Query Result
![Page 6: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/6.jpg)
Persistence
Object meets Database
Put a square in a round JDBC is not a Persistence
Framework Object-relational Mapping
Container Managed Persistence Java Data Objects
![Page 7: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/7.jpg)
Persistence – Entity Bean CMP
<enterprise-beans> <entity> <description>Models a music CD</description> <ejb-name>CDBean</ejb-name> <home>com.web_tomorrow.cd.CDHome</home> <remote>com.web_tomorrow.cd.CD</remote> <ejb-class>com.web_tomorrow.cd.CDBean</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.String</prim-key-class> <reentrant>False</reentrant> <cmp-field><field-name>id</field-name></cmp-field> <cmp-field><field-name>title</field-name></cmp-field> <cmp-field><field-name>artist</field-name></cmp-field> <primkey-field>id</primkey-field> </entity></entity-beans>
![Page 8: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/8.jpg)
Persistence – JDO
Java Data Objects Transparent database access Code that accesses the underlying data
store, without using database-specific code Use of JDO in J2EE
Persistent helper classes for Session Beans Delegate classes for Bean or Container
Managed Persistence Entity Beans
![Page 9: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/9.jpg)
JDBC Based Frameworks
Everyone (… and their Grandmother) writes JDBC based Persistence Libraries Free or Commercial Often with IDE integration Examples
Borland Oracle Compiere (no IDE)
30 % of typical IT project:
DB programming & persistence mgt
![Page 10: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/10.jpg)
Agenda -- Performance
What I do Persistence Performance JDBC
Connection Query Result
![Page 11: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/11.jpg)
Performance Is a side effect of good OO design
Schema should be ≥ 3rd Normal Form That is want Optimizers recognize
For high-end databases (e.g. Oracle) there is no need for de-normalization
Performance Rules #1 Clean design #2 Tweak performance
Don’t Design for Performance
![Page 12: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/12.jpg)
Performance Rules
Tune the Design Tune the Application Tune the OS Tune Memory Structure Tune Disk I/O Usage Detect and Eliminate Resource
Contention
![Page 13: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/13.jpg)
Performance Design Options Data Intensive Routines in DB Server
Within DB Engine - Low Switching Cost No Communication Overhead PL/SQL, Java Stored Procedures, C++, …
“Automatic” data consistency (Helps the Optimizer) Explicit Constraints Trigger (in PL/SQL, Java, …)
![Page 14: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/14.jpg)
DB Programming Options Set Processing outperforms everything
else Not your beginner SQL
Complex UPDATE statements
PL/SQL Dependency Management (*) Data intensive manipulations
Java Stored Procedures Special VMs – Upload, Debugging Special JDBC Driver “always connected”
![Page 15: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/15.jpg)
DB Programming Options
SQLJ Java Language SQL Pre-compiler High level SQLJ-Language Converted to “normal” JDBC code Supported by IBM, Oracle, …
![Page 16: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/16.jpg)
Question:
Is SQL a Standard?
![Page 17: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/17.jpg)
DB Independence Lowest common denominator
Use Access Functionality with Oracle ?
You have to design & program differently for the database / camps Oracle, DB2, (PostgreSQL), … Microsoft SQL Server, Sybase, … MS Access, mySQL, …
![Page 18: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/18.jpg)
DB Incompatibilities (1) SQL join
SELECT .. FROM a JOIN b ON (a.id=b.id); SELECT .. FROM a LEFT OUTER JOIN b ON (a.id=b.id); SELECT .. FROM a, b WHERE a.id=b.id(+); SELECT .. FROM a, b WHERE a.id=*b.id;
Sub-Query SELECT .. FROM a, (SELECT .. FROM b) AS b WHERE
… Pseudo-Columns
ROWNUM, SysDate LIMIT
Reserved Words – Optional Syntax – Alias
![Page 19: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/19.jpg)
DB Incompatibilities (2) Data Types
Not just naming (NUMBER vs. DECIMAL) BLOB, CLOB, …
PL/SQL - Syntax/Functionality for Packages, Functions, Procedures, Triggers, … Commit Strategy Exception (raise & catch)
Data Dictionary OLAP Support
Materialized Views, …
![Page 20: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/20.jpg)
DB Independence – cont. DB Management Layer
Lowest Common SQL Denominator Alternative
Application Dependent Uses Database specific Functionality
Examples: SAP’s DB Access Layer (CMP Products) Compiere’s dbPort …
Oracle PG
dbPort
Application
I am talking
to Oracle
DB/2 ?
![Page 21: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/21.jpg)
Performance Reality You can’t have everything
High functionality & Ability to fine-tune Database independence
Automatic Object Mapping
You need skill-mix (Traditional) Database Design Database Programming Environment Object Design Java Programming Environment
![Page 22: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/22.jpg)
The Test Environment Windows 2000 SP 2 Server
Dual 450MHz CPU Oracle 8.1.7 (parallel option not enabled) JDBC driver for 9i
RedHat Linux 7.2 Single 500 MHz CPU PostgreSQL 7.1.3 JDBC driver for 7.2
Java 1.4.0 SELECT * FROM AD_Column;
4,800 Rows - 38 Columns – ca. 1,2 MB
Testing DB Driver
![Page 23: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/23.jpg)
Agenda -- JDBC
What I do Persistence Performance JDBC
Connection Query Result
![Page 24: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/24.jpg)
Connecting
Connection Options java.sql.Driver &
java.sql.DriverManager javax.sql.DataSource javax.sql.PooledConnection Connection Cache
![Page 25: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/25.jpg)
JDBC Driver Types
Type 4Pure Java
Type 3Type 1 Type 2
![Page 26: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/26.jpg)
Connecting .. Driver Type 1..4 Driver
Type 4: Pure Java Driver DB/2 does not provide Type 4 driver
Interface java.sql.Driver Explicitly
Class.forName(“org.postgresql.Driver"); new oracle.jdbc.OracleDriver();
System Properties jdbc.drivers=
oracle.jdbc.OracleDriver:org.postgresql.Driver
![Page 27: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/27.jpg)
Connecting DriverManager Basic service for managing a set of JDBC
drivers JDBC Drivers register with the DriverManager Source
Connection c = DriverManager.getConnection ("jdbc:oracle:thin:@dev:1521:dev1");
Connection Strings jdbc:oracle:oci8:@ -- (#1) Bequeath jdbc:oracle:oci8:@ora -- (#3) Net8 jdbc:oracle:thin:@dev:1521:ora -- (#2) Type 4 jdbc:postgresql://dev:5432/compiere
![Page 28: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/28.jpg)
Connecting .. Data Source Interface javax.sql.DataSource
Alternative to DriverManager Based on JNDI
Server Source DataSource ds = new OracleDataSource();
Context ctx = new Initial Context();ctx.bind (“jdbc/myDB”, ds);
Client Source Context ctx = new Initial Context();
DataSource ds = (DataSource)ctx.lookup(“jdbc/myDB”);Connection c = ds.getConnection();
![Page 29: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/29.jpg)
Connection .. Pool Optional extension One Physical Connection Multiple Logical Connection
Connection.close() makes it available Eliminates overhead of creating physical
connection: Connection c = pool.getConnection();
Statement stmt = c.createStatement(..);stmt.close();c.close();
![Page 30: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/30.jpg)
Connection .. Cache
Optional Extension … no standard (!)
Multiple Physical Connections We wrote own Connection Cache
Connection not closed in code
![Page 31: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/31.jpg)
Multi-Thread
Most JDBC implementations synchronize on Connection You need multiple connections for
multi-threaded applications Compiere uses
2 “read-only” never closed Cached Connections (w. health check)
1 “update” never closed Connection
![Page 32: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/32.jpg)
Test Results (10 Threads)
0
200
400
600
800
1,000
1,200
1,400
1,600
1,800
2,000
Net
Avg
Tim
e per
pro
cess
(m
s)
Shared Multiple MultiplePreCreated
Data Source Connection Cache
Oracle Oracle Yield PostgreSQL PostgreSQL Yield
![Page 33: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/33.jpg)
Test Results (10 Threads) Multiple Pre-Created Connections
No xxx.getConnection() Yield ‘surprise’
while (rs.next) yield();
(High) switching costs Database has result in buffer
just transmitting
Connection Cache Optimum Cache Size 2-4
![Page 34: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/34.jpg)
Test Results Oracle
Use Thin Driver jdbc:oracle:thin:@dev:1521:ora
Fastest Driver – Bequeath "jdbc:oracle:oci8:@“ 20-40% faster – Only works if only one instance
installed For Server Connections
Slowest Driver – Net8 jdbc:oracle:oci8:@ora 10-20% slower Net8 conversion Overhead
![Page 35: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/35.jpg)
Connection – Trx Level Transaction Isolation Level
TRANSACTION_NONE Not supported
TRANSACTION_READ_UNCOMMITTED Get: dirty, non-repeatable, phantom reads
TRANSACTION_READ_COMMITTED No: dirty reads (uncommitted data) Get: non-repeatable, phantom reads
TRANSACTION_REPEATABLE_READ No: dirty, non-repeatable (data changed) reads Get: phantom reads
TRANSACTION_SERIALIZABLE No: dirty reads, non-repeatable, phantom (WHERE
changed) reads
![Page 36: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/36.jpg)
Connection You probably want to get the latest data
conn.setTransactionIsolation (Connection.TRANSACTION_READ_COMMITTED);
Data updated by others & Triggers Set explicitly
Connection is Interface Default set by Vendor Implementation
Consider Read Only – Hint to Driver/Database
conn.setReadOnly(true);
![Page 37: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/37.jpg)
Querying Statement – conn.createStatement()
ResultSet.TYPE_ FORWARD_ONLY, SCROLL_INSENSITIVE,
SCROLL_SENSITIVE ResultSet.CONCUR_
READ_ONLY or _UPDATABLE Prepared Statement – conn.prepareStatement()
Database dependent statement caching “UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?” pstmt.setDataType (pos, value);
Callable Statement – conn.prepareCall() “{?= call myProcedure (?,?)}”
Batch [implementation not mandatory] stmt.addBatch(“Insert ..”); stmt.executeBatch();
![Page 38: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/38.jpg)
setDataType Conversion
![Page 39: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/39.jpg)
Result - ResultSet Created by stmt.executeQuery() Fetch Size
rs.setFetchSize(int); stmt.setFetchSize(int); VendorConnection.setFetchSize(int); Driver hint
Non updateable RS Forward only & read just once
Data Retrieval getDataType (int pos); getDataType (String columnName); wasNull();
No way toCancel aQuery
![Page 40: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/40.jpg)
getDataSet Conversion
![Page 41: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/41.jpg)
ResultSet Navigate
absolute(int), relative(int) – first(), last(), next(), previous()
Update updateDataType (pos, data);
updateDataType (columnName, data); updateRow(); – or – cancelRowUpdates();
Insert moveToInsertRow();
updateDataTypeinsertRow(); – or – cancelRowUpdates();
moveToCurrentRow(); Delete
deleteRow(); Refresh
refreshRow();
![Page 42: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/42.jpg)
Result - RowSet JavaBean - Extends ResultSet Types – Implementation Optional
Cached – serialized - disconnected - X-JVM - Synchronize
JDBC Web
Code Example RowSet rowset = new OracleCachedRowSet();
rowset.setUrl (CONNECTION);rowset.setUsername (UID);rowset.setPassword (PWD);rowset.setFetchSize (fetchSize);rowset.setCommand (SQL_STATEMENT);rowset.execute ();
Client … RMI … no
JDBC
![Page 43: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/43.jpg)
Meta Data Interfaces DatabaseMetaData
Driver JDBC compatibility Data Dictionary Queries
ParameterMetaData Prepared Statement
ResultSetMetaData Get Column Name, Data Type of
“SELECT *” query
![Page 44: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/44.jpg)
Connection - Query - Retrieve
0
50
100
150
200
250
300
350
400
ms
Connection DataSourceCon
Cache
Con Cache JDBC RowSet CachedRowSet
PostgreSQL
Connect Query* Retrieve/ 10
First Connection, (Query) & Retrieval took significantly longer – 4,800 records
![Page 45: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/45.jpg)
Fetch Size
0
500
1,000
1,500
2,000
2,500
3,000
3,500
4,000
ms
Connection Data Source Con Cache JDBC RowSet CachedRowSet
PostgreSQL
Connect Query Retrieve Fetch=10 Retrieve Fetch=20
![Page 46: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/46.jpg)
Compiere JDBC Use Async worker to load data Select
Prepared Statement - Read-Only, Forward Only Statement & ResultSet (cursor) closed after read
Insert ResultSet from Prepared Statement (WHERE 1=2) rs.moveToInsertRow(); rs.insertRow(); Re-read from Database using PK (trigger, ..)
Update Re-read using ROWID or OID (“for update”) Check, if data* not changed
rs.updateXX(); rs.updateRow(); or rs.cancelRowUpdates();
Re-read from Database using PK
deterministic, optimistic
locking
![Page 47: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/47.jpg)
Performance Hints Qualify Columns in SELECT Clause
Gets only data needed Use position of Column – not Name
rs.getString(1); Give as much info as possible
WHERE clause Scrollable/Updateable/…
Use Locks carefully … and release them explicitly
Close Statements & ResultSets – closes DB cursors
rs.close(); stmt.close();
![Page 48: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/48.jpg)
Performance Hints (2) Use Batches where possible Connection.setAutoCommit (false) Database Meta-Data Queries are slow
Quantify arguments, search patterns Consider Use of
Statement.setFetchSize (int no) Statement.setMaxRows (int no) Statement.setQueryTimeout (int seconds) Statement.setMaxFieldSize (int byte) Hidden unique ID: RowID, OID RowSet for complex mass update
Use Prepared Statements (*)
![Page 49: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/49.jpg)
Resources (Selection) JDBC
http://java.sun.com/products/jdbc Several courses and introductions
http://www.jguru.com/faq/JDBC http://www.javaskyline.com/learnjdbc.
html JDO
http://access1.sun.com/jdo http://www.jdocentral.com
![Page 50: High Performance Jdbc](https://reader035.vdocuments.us/reader035/viewer/2022062511/54be781e4a7959a4118b45a4/html5/thumbnails/50.jpg)
Thanks You can download the presentation
http://www.compiere.org/download/ If you have questions
[email protected] (203) 445-8182
Have a look http://www.compiere.com/consulting.h
tml