Transcript Parse Test

My JDBC Performance Sucks and
What I Did About It!
©2010 PROS Holdings, Inc. All rights reserved.
Who am I?
 Peter Tran
 Senior Performance Technical Lead
 Over 12 years with PROS
 8 years working on server-side performance
 6 years Oracle tuning
 3 years SQL Server tuning
 [email protected]
©2010 PROS Holdings, Inc. All rights reserved.
2
Topics
 Assumptions
 Transaction Isolation Levels
 Connection
 Batching
 Queries
 Parsing
 Misc
©2010 PROS Holdings, Inc. All rights reserved.
3
Assumptions
 Will not compare different driver type
 Recommendations should be driver type agnostic
 Examples in presentation uses Type IV Thin Driver
 Examples uses SQL Server 2008 and Oracle 10g
databases
 Will not cover SQL optimization
 Will not cover JDBC resource leak
©2010 PROS Holdings, Inc. All rights reserved.
4
Transaction Isolation Levels
 Big impact on performance
 Critical for correct business logic
 java.sql.Connection
Transaction Isolation Level
TRANSACTION_NONE
TRANSACTION_READ_UNCOMMITED
TRANSACTION_READ_COMMITTED
TRANSACTON_REPEATABLE_READ
TRANSACTION_SERIALIZABLE
©2010 PROS Holdings, Inc. All rights reserved.
Dirty-Reads Non-Repeatable
Phantom
Performance
N/A
N/A
N/A
Fastest
Yes
Yes
Yes
Fast
No
Yes
Yes
Medium
No
No
Yes
Slow
No
No
No
Slowest
5
Connection
 java.sql.Connection
 Use connection pooling
 Big performance penalty to create new physical connection to
database
Time to Create Physical Connection
7
6
Seconds
5
4
3
2
1
0
1
1000
# of Physical Database Connections
©2010 PROS Holdings, Inc. All rights reserved.
6
Batching
 Batch UPDATE and INSERT SQLs
 Use




 java.sql.(Prepared/Callable)Statement
 Added benefit of preventing SQL Injection (security)
Do not use
 java.sql.Statement
Remember to setAutoCommit to FALSE
SQL statement reuse
Reduces parse
©2010 PROS Holdings, Inc. All rights reserved.
7
Batch Insert Example – Wrong!
 Wrong way to do batch update.
//turn off autocommit
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
©2010 PROS Holdings, Inc. All rights reserved.
8
Batch Insert Example
 Right way to do batch update using PreparedStatement
// turn off autocommit
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement( "INSERT INTO employees VALUES (?, ?)");
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
©2010 PROS Holdings, Inc. All rights reserved.
9
Batch Query Example
 Right way to do batch query using CallableStatement
// turn off autocommit
con.setAutoCommit(false);
CallableStatement stmt = con.prepareCall("sp_GetEmployees (?, ?)");
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
ResultSet rs = stmt.executeQuery()
©2010 PROS Holdings, Inc. All rights reserved.
10
Batch Insert Test Results
Batch Tests
7
6
Seconds
5
4
3
2
1
0
1
2
3
4
Test #
 Test 1: Statement – No Batch
 Test 2: PreparedStatement – No batch/no reuse
 Test 3: PreparedStatement – No batch/reuse
 Test 4: PreparedStatement – With batch/reuse
©2010 PROS Holdings, Inc. All rights reserved.
11
Batch Pitfalls
 Forgetting to set autocommit to FALSE
 Excessively large batch size
 Writer blocks readers
 Use up more memory on database server
 ORA-01555 snapshot too old
©2010 PROS Holdings, Inc. All rights reserved.
12
Query Performance
 Set fetch size
 Settable







java.sql.ResultSet
java.sql.Statement
java.sql.(Prepared/Callable)Statement
Oracle 11.2g driver defaults to 10
SQL Server v3.0 driver defaults to 128
Reduces Physical/Logical I/O
Uses more memory in client to hold data
©2010 PROS Holdings, Inc. All rights reserved.
13
Query Performance
 Fetch only data that is needed
 SQL Optimization – Physical/Logical IOs
 Reduce Network
 Use appropriate getXXX() method for retrieving data
 Minimize data conversion guessing
 Eliminates nasty conversion bug
©2010 PROS Holdings, Inc. All rights reserved.
14
Microsoft Query Set Fetch Size Test Results
Microsoft Query Fetch Size Tests
0.3
Seconds
0.25
0.2
0.15
0.1
0.05
0
1
2
3
Test #
 Test 1: Query 1M rows – fetch size = 10
 Test 2: Query 1M rows – fetch size = 128 (default)
 Test 3: Query 1M rows – fetch size = 1M
©2010 PROS Holdings, Inc. All rights reserved.
15
Oracle Query Set Fetch Size Test Results
Oracle Query Fetch Size Tests
0.25
Seconds
0.2
0.15
0.1
0.05
0
1
2
3
Test #
 Test 1: Query 1M rows – fetch size = 10 (default)
 Test 2: Query 1M rows – fetch size = 500
 Test 3: Query 1M rows – fetch size = 1M
©2010 PROS Holdings, Inc. All rights reserved.
16
Parsing
 Parsing is expensive!
 Scalability killer for high volume OLTP applications
 Not as big of concern for OLAP applications
 Consumes CPU intensive
 Database engine not doing real-work
 Increase latch contention
 Increase memory usage
 SQL statement cache is a singleton
 Oracle: Shared Pool
 SQL Server: Plan Cache
©2010 PROS Holdings, Inc. All rights reserved.
17
Parsing
 Best place to fix is in the application code
 Use PreparedStatement or CallableStatement
 No Parse – Predicates uses bind variables and
Statement reused
 Soft Parse – Predicate uses bind variables but
Statement not reused
 Hard Parse – Predicate with literal values
Parse Type
Parse Once
Soft Parse
Hard Parse
©2010 PROS Holdings, Inc. All rights reserved.
Performance
Best
Good
Bad
18
Parse Fix - Hack
 Do not have access to application code
 May select a suboptimal query plan
 Buggy
©2010 PROS Holdings, Inc. All rights reserved.
19
Oracle Parsing Example
 Oracle 11.2g JDBC Driver
Parse Test
12
Seconds
10
8
6
4
2
0
1
2
3
Test #
 Test 1: Hard Parse (using Statement with literals)
 Test 2: Soft Parse
 Test 3: Parse Once
©2010 PROS Holdings, Inc. All rights reserved.
20
Microsoft Parsing Example
 Microsoft JDBC Driver version 3.0
Seconds
Parse Test
7
6
5
4
3
2
1
0
1
2
3
Test #
 Test 1: Hard Parse (using Statement with literals)
 Test 2: Soft Parse
 Test 3: Parse Once
©2010 PROS Holdings, Inc. All rights reserved.
21
jTDS Parsing Example for SQL Server
 jTDS JDBC Driver version 1.2.5
Seconds
Parse Test
4.5
4
3.5
3
2.5
2
1.5
1
0.5
0
1
2
3
Test #
 Test 1: Hard Parse (using Statement with literals)
 Test 2: Soft Parse
 Test 3: Parse Once
©2010 PROS Holdings, Inc. All rights reserved.
22
Parse Fix - Hack
 SQL Server
 Use PARAMETERIZATION = FORCED
 Set at database level (Database Properties -> Options)
 Restrictive: Check Books-On-Line (BOL)
 YMMV – 5% improvements
©2010 PROS Holdings, Inc. All rights reserved.
23
Parse Fix - Hack
 Oracle
 CURSOR_SHARING = SIMILAR | FORCED


Instance level or session level
Use trigger to hack session level for each login
CREATE OR REPLACE TRIGGER after_usr_logon
AFTER LOGON ON <USER>.SCHEMA
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=FORCE';
END;




Super buggy
Use SIMILAR – mixed work-load OLTP/OLAP
Use FORCE – pure OLTP
Use EXACT (default) – DSS/DW OLAP
©2010 PROS Holdings, Inc. All rights reserved.
24
Driver Specific Improvements
 Requires casting to drive specific implementation
 Your Performance May Vary (YPMV)
 Microsoft JDBC Driver version 3.0
 Adaptive Buffer – reduces memory footprint in client to
minimize Out of Memory but requires more round-trip to
database (default=adaptive as of version 2.0)
 Oracle 11.2g JDBC Driver
 Statement and ResultSet Caching
 Connection Caching
 jTDS JDBC Driver version 1.2.5
 Implicitly caches PreparedStatement – not a new
feature
©2010 PROS Holdings, Inc. All rights reserved.
25
Database JDBC Driver Tuning Parameters
 SQL Server
 Implicit Unicode conversion


Use setStringParametersAsUnicode
Bypass index and use table scan
©2010 PROS Holdings, Inc. All rights reserved.
26
My JDBC Performance Workout Plan
 Maximize the usage of batching
 Set appropriate fetch size for queries
 Set the appropriate isolation level
 Reduce the hard/soft parses
Before
©2010 PROS Holdings, Inc. All rights reserved.
After
27
Q&A
©2010 PROS Holdings, Inc. All rights reserved.
28