001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.bench;
007:
008: import java.math.BigDecimal;
009: import java.sql.PreparedStatement;
010: import java.util.Random;
011:
012: /**
013: * This test is similar to the TPC-A test of the Transaction Processing Council
014: * (TPC). However, only one connection and one thread is used.
015: * <p>
016: * See also: http://www.tpc.org/tpca/spec/tpca_current.pdf
017: */
018: public class BenchA implements Bench {
019:
020: private Database db;
021:
022: private int branches;
023: private int tellers;
024: private int accounts;
025: private int size;
026:
027: private static final String FILLER = "abcdefghijklmnopqrstuvwxyz";
028: private static final int DELTA = 10000;
029:
030: public void init(Database db, int size) throws Exception {
031: this .db = db;
032: this .size = size;
033:
034: int scale = 1;
035: accounts = size * 50;
036: tellers = Math.max(accounts / 10, 1);
037: branches = Math.max(tellers / 10, 1);
038:
039: db.start(this , "Init");
040:
041: db.openConnection();
042:
043: db.dropTable("BRANCHES");
044: db.dropTable("TELLERS");
045: db.dropTable("ACCOUNTS");
046: db.dropTable("HISTORY");
047:
048: String[] create = {
049: "CREATE TABLE BRANCHES(BID INT NOT NULL PRIMARY KEY, BBALANCE DECIMAL(15,2), FILLER VARCHAR(88))",
050: "CREATE TABLE TELLERS(TID INT NOT NULL PRIMARY KEY, BID INT, TBALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
051: "CREATE TABLE ACCOUNTS(AID INT NOT NULL PRIMARY KEY, BID INT, ABALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
052: "CREATE TABLE HISTORY(TID INT, BID INT, AID INT, DELTA DECIMAL(15,2), HTIME DATETIME, FILLER VARCHAR(40))" };
053:
054: for (int i = 0; i < create.length; i++) {
055: db.update(create[i]);
056: }
057:
058: PreparedStatement prep;
059: db.setAutoCommit(false);
060: int commitEvery = 1000;
061: prep = db
062: .prepare("INSERT INTO BRANCHES(BID,BBALANCE,FILLER) VALUES(?,10000.00,'"
063: + FILLER + "')");
064: for (int i = 0; i < branches * scale; i++) {
065: prep.setInt(1, i);
066: db.update(prep, "insertBranches");
067: if (i % commitEvery == 0) {
068: db.commit();
069: }
070: }
071: db.commit();
072: prep = db
073: .prepare("INSERT INTO TELLERS(TID,BID,TBALANCE,FILLER) VALUES(?,?,10000.00,'"
074: + FILLER + "')");
075: for (int i = 0; i < tellers * scale; i++) {
076: prep.setInt(1, i);
077: prep.setInt(2, i / tellers);
078: db.update(prep, "insertTellers");
079: if (i % commitEvery == 0) {
080: db.commit();
081: }
082: }
083: db.commit();
084: int len = accounts * scale;
085: prep = db
086: .prepare("INSERT INTO ACCOUNTS(AID,BID,ABALANCE,FILLER) VALUES(?,?,10000.00,'"
087: + FILLER + "')");
088: for (int i = 0; i < len; i++) {
089: prep.setInt(1, i);
090: prep.setInt(2, i / accounts);
091: db.update(prep, "insertAccounts");
092: if (i % commitEvery == 0) {
093: db.commit();
094: }
095: }
096: db.commit();
097: db.closeConnection();
098: db.end();
099:
100: // db.start(this, "Open/Close");
101: // db.openConnection();
102: // db.closeConnection();
103: // db.end();
104: }
105:
106: public void runTest() throws Exception {
107:
108: db.start(this , "Transactions");
109: db.openConnection();
110: processTransactions();
111: db.closeConnection();
112: db.end();
113:
114: db.openConnection();
115: processTransactions();
116: db.logMemory(this , "Memory Usage");
117: db.closeConnection();
118:
119: }
120:
121: private void processTransactions() throws Exception {
122: Random random = db.getRandom();
123: int branch = random.nextInt(branches);
124: int teller = random.nextInt(tellers);
125: int transactions = size * 30;
126:
127: PreparedStatement updateAccount = db
128: .prepare("UPDATE ACCOUNTS SET ABALANCE=ABALANCE+? WHERE AID=?");
129: PreparedStatement selectBalance = db
130: .prepare("SELECT ABALANCE FROM ACCOUNTS WHERE AID=?");
131: PreparedStatement updateTeller = db
132: .prepare("UPDATE TELLERS SET TBALANCE=TBALANCE+? WHERE TID=?");
133: PreparedStatement updateBranch = db
134: .prepare("UPDATE BRANCHES SET BBALANCE=BBALANCE+? WHERE BID=?");
135: PreparedStatement insertHistory = db
136: .prepare("INSERT INTO HISTORY(AID,TID,BID,DELTA,HTIME,FILLER) VALUES(?,?,?,?,?,?)");
137: int accountsPerBranch = accounts / branches;
138: db.setAutoCommit(false);
139:
140: for (int i = 0; i < transactions; i++) {
141: int account;
142: if (random.nextInt(100) < 85) {
143: account = random.nextInt(accountsPerBranch) + branch
144: * accountsPerBranch;
145: } else {
146: account = random.nextInt(accounts);
147: }
148: int max = BenchA.DELTA;
149: // delta: -max .. +max
150:
151: BigDecimal delta = new BigDecimal(""
152: + (random.nextInt(max * 2) - max));
153: long current = System.currentTimeMillis();
154:
155: updateAccount.setBigDecimal(1, delta);
156: updateAccount.setInt(2, account);
157: db.update(updateAccount, "updateAccount");
158:
159: updateTeller.setBigDecimal(1, delta);
160: updateTeller.setInt(2, teller);
161: db.update(updateTeller, "updateTeller");
162:
163: updateBranch.setBigDecimal(1, delta);
164: updateBranch.setInt(2, branch);
165: db.update(updateBranch, "updateBranch");
166:
167: selectBalance.setInt(1, account);
168: db.queryReadResult(selectBalance);
169:
170: insertHistory.setInt(1, account);
171: insertHistory.setInt(2, teller);
172: insertHistory.setInt(3, branch);
173: insertHistory.setBigDecimal(4, delta);
174: // TODO convert: should be able to convert date to timestamp
175: // (by using 0 for remaining fields)
176: // insertHistory.setDate(5, new java.sql.Date(current));
177: insertHistory.setTimestamp(5, new java.sql.Timestamp(
178: current));
179: insertHistory.setString(6, BenchA.FILLER);
180: db.update(insertHistory, "insertHistory");
181:
182: db.commit();
183: }
184: updateAccount.close();
185: selectBalance.close();
186: updateTeller.close();
187: updateBranch.close();
188: insertHistory.close();
189: }
190:
191: public String getName() {
192: return "BenchA";
193: }
194:
195: }
|