1 / 28

My JDBC Performance Sucks and What I Did About It!

My JDBC Performance Sucks and What I Did About It!. 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 ptran@prospricing.com. Topics. Assumptions

kristy
Download Presentation

My JDBC Performance Sucks and What I Did About It!

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. My JDBC Performance Sucks and What I Did About It!

  2. 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 • 3years SQL Server tuning • ptran@prospricing.com

  3. Topics • Assumptions • Transaction Isolation Levels • Connection • Batching • Queries • Parsing • Misc

  4. 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

  5. Transaction Isolation Levels • Big impact on performance • Critical for correct business logic • java.sql.Connection

  6. Connection • java.sql.Connection • Use connection pooling • Big performance penalty to create new physical connection to database

  7. 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

  8. 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();

  9. 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();

  10. 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()

  11. Batch Insert Test Results • Test 1: Statement – No Batch • Test 2: PreparedStatement – No batch/no reuse • Test 3: PreparedStatement – No batch/reuse • Test 4: PreparedStatement – With batch/reuse

  12. 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

  13. 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

  14. 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

  15. Microsoft Query Set Fetch Size Test Results • 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

  16. Oracle Query Set Fetch Size Test Results • 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

  17. 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

  18. 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

  19. Parse Fix - Hack • Do not have access to application code • May select a suboptimal query plan • Buggy

  20. Oracle Parsing Example • Oracle 11.2g JDBC Driver • Test 1: Hard Parse (using Statement with literals) • Test 2: Soft Parse • Test 3: Parse Once

  21. Microsoft Parsing Example • Microsoft JDBC Driver version 3.0 • Test 1: Hard Parse (using Statement with literals) • Test 2: Soft Parse • Test 3: Parse Once

  22. jTDS Parsing Example for SQL Server • jTDS JDBC Driver version 1.2.5 • Test 1: Hard Parse (using Statement with literals) • Test 2: Soft Parse • Test 3: Parse Once

  23. Parse Fix - Hack • SQL Server • Use PARAMETERIZATION = FORCED • Set at database level (Database Properties -> Options) • Restrictive: Check Books-On-Line (BOL) • YMMV – 5% improvements

  24. Parse Fix - Hack • Oracle • CURSOR_SHARING = SIMILAR | FORCED • Instance level or session level • Use trigger to hack session level for each login CREATEORREPLACETRIGGER 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

  25. 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

  26. Database JDBC Driver Tuning Parameters • SQL Server • Implicit Unicode conversion • Use setStringParametersAsUnicode • Bypass index and use table scan

  27. 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 After

  28. Q&A

More Related