java绑定变量优点_java jdbc 绑定变量与不绑定性能对比

package com.fuyou.demo;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import com.fuyou.jdbc.JRunstats;

import com.fuyou.util.JDBCUtil;

public class DemoBind {

public static void main(String[] args) throws Exception {

Connection conn = null;

try {

conn = JDBCUtil.getConnection("fuyou", "fuyou", "orcl");

JRunstats.prepareBenchmarkStatements(conn);

JRunstats.markStart(conn);

_insertWithBind(conn);

JRunstats.markMiddle(conn);

_insertWithoutBind(conn);

JRunstats.markEnd(conn);

} catch (SQLException e) {

// handle the exception properly - in this case, we just

// print the stack trace.

JDBCUtil.printException(e);

} finally {

// release the JDBC resources in the finally clause.

JRunstats.closeBenchmarkStatements(conn);

JDBCUtil.close(conn);

}

}

private static void _insertWithBind(Connection conn) throws SQLException {

PreparedStatement pstmt = null;

try {

pstmt = conn.prepareStatement("insert into t1(x) values( ? ) ");

for (int i = 0; i < 10000; i++) {

pstmt.setInt(1, i);

pstmt.executeUpdate();

}

} finally {

// release JDBC related resources in the finally clause.

JDBCUtil.close(pstmt);

}

}

private static void _insertWithoutBind(Connection conn) throws SQLException {

Statement stmt = null;

try {

stmt = conn.createStatement();

for (int i = 0; i < 10000; i++) {

stmt.executeUpdate("insert into t1( x ) values( " + i + ")");

}

} finally {

// release JDBC related resources in the finally clause.

JDBCUtil.close(stmt);

}

}

}

结果

数据库URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=127.0.0.1))(CONNECT_DATA=(SID=orcl)))

------- Benchmark Results --------

Results from RUNSTATS utility

Run1 ran in 116 hsecs

Run2 ran in 679 hsecs

run 1 ran in 17.08% of the time

Name Run1 Run2 Diff

STAT...opened cursors cumulati 3 4 1

STAT...change write time 2 3 1

STAT...calls to kcmgcs 19 20 1

STAT...deferred (CURRENT) bloc 1 0 -1

STAT...table scans (short tabl 0 1 1

STAT...session cursor cache co 0 1 1

STAT...execute count 10,002 10,003 1

LATCH.session allocation 3 2 -1

LATCH.session idle bit 20,006 20,005 -1

LATCH.ksuosstats global area 0 1 1

LATCH.Consistent RBA 3 4 1

LATCH.sort extent pool 1 0 -1

LATCH.In memory undo latch 2 3 1

LATCH.KTF sga latch 1 0 -1

LATCH.Shared B-Tree 0 1 1

LATCH.kwqbsn:qsga 0 1 1

LATCH.mostly latch-free SCN 4 2 -2

STAT...no work - consistent re 0 2 2

LATCH.lgwr LWN SCN 4 2 -2

LATCH.FAL subheap alocation 0 2 2

LATCH.FAL request queue 0 2 2

STAT...table scan blocks gotte 0 2 2

LATCH.reservation so alloc lat 0 2 2

LATCH.OS process allocation 0 2 2

STAT...redo ordering marks 79 77 -2

STAT...redo log space requests 0 2 2

STAT...redo buffer allocation 0 2 2

STAT...calls to kcmgas 79 77 -2

STAT...active txn count during 16 19 3

LATCH.post/wait queue 0 3 3

LATCH.Reserved Space Latch 0 3 3

LATCH.archive process latch 1 4 3

STAT...consistent gets - exami 16 19 3

LATCH.KMG MMAN ready and start 0 3 3

LATCH.session state list latch 3 0 -3

LATCH.dml lock allocation 3 0 -3

STAT...cleanout - number of kt 16 19 3

LATCH.ktm global data 3 0 -3

LATCH.session timer 0 3 3

LATCH.archive control 0 3 3

LATCH.messages 60 64 4

STAT...redo log space wait tim 0 4 4

LATCH.qmn task queue latch 0 4 4

LATCH.active checkpoint queue 0 5 5

STAT...consistent changes 36 31 -5

LATCH.FOB s.o list latch 1 7 6

LATCH.file cache latch 7 1 -6

LATCH.FIB s.o chain latch 0 6 6

LATCH.redo writing 14 21 7

LATCH.active service list 0 18 18

LATCH.undo global data 43 25 -18

LATCH.channel operations paren 46 28 -18

STAT...table scan rows gotten 0 22 22

LATCH.redo allocation 50 74 24

LATCH.checkpoint queue latch 17 49 32

STAT...workarea memory allocat -35 5 40

LATCH.cache buffers lru chain 87 138 51

STAT...free buffer requested 84 138 54

LATCH.SQL memory manager worka 73 140 67

LATCH.JS queue state obj latch 0 72 72

STAT...redo entries 10,054 10,129 75

STAT...db block changes 20,172 20,247 75

LATCH.cache buffer handles 0 88 88

STAT...undo change vector size 641,872 641,964 92

STAT...recursive cpu usage 0 179 179

STAT...parse time cpu 0 460 460

STAT...parse time elapsed 0 476 476

STAT...CPU used when call star 33 540 507

STAT...CPU used by this sessio 33 540 507

STAT...DB time 30 553 523

STAT...Elapsed Time 118 679 561

LATCH.object queue header oper 1,091 292 -799

LATCH.simulator hash latch 2,018 688 -1,330

LATCH.simulator lru latch 2,013 671 -1,342

STAT...redo size 2,370,844 2,377,128 6,284

STAT...enqueue requests 11 10,009 9,998

STAT...parse count (hard) 1 10,000 9,999

STAT...enqueue releases 9 10,009 10,000

STAT...parse count (total) 3 10,003 10,000

STAT...calls to get snapshot s 3 10,007 10,004

STAT...recursive calls 3 10,011 10,008

STAT...consistent gets 22 10,034 10,012

STAT...consistent gets from ca 22 10,034 10,012

STAT...bytes sent via SQL*Net 515,940 526,197 10,257

STAT...session uga memory 96 15,072 14,976

STAT...db block gets from cach 10,277 30,356 20,079

STAT...db block gets 10,277 30,356 20,079

LATCH.enqueue hash chains 62 20,203 20,141

LATCH.enqueues 54 20,203 20,149

STAT...session logical reads 10,299 40,390 30,091

LATCH.kks stats 2 39,808 39,806

LATCH.library cache pin 20,025 70,037 50,012

LATCH.cache buffers chains 52,574 111,156 58,582

LATCH.library cache lock 14 60,017 60,003

LATCH.row cache objects 30 120,021 119,991

STAT...session pga memory max 262,144 65,536 -196,608

LATCH.shared pool 10,033 211,340 201,307

LATCH.library cache 20,056 234,174 214,118

STAT...session uga memory max 261,964 96 -261,868

STAT...bytes received via SQL* 499,935 808,997 309,062

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

128,408 909,402 780,994 14.12%

Runtime Execution Time Differences as seen by the client

Run1 ran in 115 hsecs

Run2 ran in 681 hsecs

Run1 ran in 17% of the time

系统：xp

数据库oracle 10.2