1 5 package org.h2.test.bench; 6 7 import java.sql.*; 8 import java.math.*; 9 10 public class BenchC implements Bench { 11 12 private Database db; 13 14 int warehouses = 1; 15 int items = 10000; 16 int districtsPerWarehouse = 10; 17 int customersPerDistrict = 300; 18 private int ordersPerDistrict = 300; 19 20 private BenchCRandom random; 21 private String action; 22 23 private int commitEvery = 1000; 24 25 private static final String [] TABLES = new String [] { "WAREHOUSE", 30 "DISTRICT", "CUSTOMER", "HISTORY", "ORDERS", "NEW_ORDER", "ITEM", 31 "STOCK", "ORDER_LINE", "RESULTS" }; 32 private static final String [] CREATE_SQL = new String [] { 33 "CREATE TABLE WAREHOUSE(\n" + " W_ID INT NOT NULL PRIMARY KEY,\n" 34 + " W_NAME VARCHAR(10),\n" + " W_STREET_1 VARCHAR(20),\n" 35 + " W_STREET_2 VARCHAR(20),\n" + " W_CITY VARCHAR(20),\n" 36 + " W_STATE CHAR(2),\n" + " W_ZIP CHAR(9),\n" 37 + " W_TAX DECIMAL(4, 4),\n" + " W_YTD DECIMAL(12, 2))", 38 "CREATE TABLE DISTRICT(\n" + " D_ID INT NOT NULL,\n" + " D_W_ID INT NOT NULL,\n" 39 + " D_NAME VARCHAR(10),\n" + " D_STREET_1 VARCHAR(20),\n" 40 + " D_STREET_2 VARCHAR(20),\n" + " D_CITY VARCHAR(20),\n" 41 + " D_STATE CHAR(2),\n" + " D_ZIP CHAR(9),\n" 42 + " D_TAX DECIMAL(4, 4),\n" + " D_YTD DECIMAL(12, 2),\n" 43 + " D_NEXT_O_ID INT,\n" 44 + " PRIMARY KEY (D_ID, D_W_ID))", "CREATE TABLE CUSTOMER(\n" + " C_ID INT NOT NULL,\n" + " C_D_ID INT NOT NULL,\n" 48 + " C_W_ID INT NOT NULL,\n" + " C_FIRST VARCHAR(16),\n" 49 + " C_MIDDLE CHAR(2),\n" + " C_LAST VARCHAR(16),\n" 50 + " C_STREET_1 VARCHAR(20),\n" 51 + " C_STREET_2 VARCHAR(20),\n" + " C_CITY VARCHAR(20),\n" 52 + " C_STATE CHAR(2),\n" + " C_ZIP CHAR(9),\n" 53 + " C_PHONE CHAR(16),\n" + " C_SINCE TIMESTAMP,\n" 54 + " C_CREDIT CHAR(2),\n" 55 + " C_CREDIT_LIM DECIMAL(12, 2),\n" 56 + " C_DISCOUNT DECIMAL(4, 4),\n" 57 + " C_BALANCE DECIMAL(12, 2),\n" 58 + " C_YTD_PAYMENT DECIMAL(12, 2),\n" 59 + " C_PAYMENT_CNT DECIMAL(4),\n" 60 + " C_DELIVERY_CNT DECIMAL(4),\n" 61 + " C_DATA VARCHAR(500),\n" 62 + " PRIMARY KEY (C_W_ID, C_D_ID, C_ID))", "CREATE INDEX CUSTOMER_NAME ON CUSTOMER(C_LAST, C_D_ID, C_W_ID)", 66 "CREATE TABLE HISTORY(\n" + " H_C_ID INT,\n" + " H_C_D_ID INT,\n" 67 + " H_C_W_ID INT,\n" + " H_D_ID INT,\n" + " H_W_ID INT,\n" 68 + " H_DATE TIMESTAMP,\n" + " H_AMOUNT DECIMAL(6, 2),\n" 69 + " H_DATA VARCHAR(24))", "CREATE TABLE ORDERS(\n" + " O_ID INT NOT NULL,\n" + " O_D_ID INT NOT NULL,\n" 75 + " O_W_ID INT NOT NULL,\n" + " O_C_ID INT,\n" 76 + " O_ENTRY_D TIMESTAMP,\n" + " O_CARRIER_ID INT,\n" 77 + " O_OL_CNT INT,\n" + " O_ALL_LOCAL DECIMAL(1),\n" 78 + " PRIMARY KEY(O_W_ID, O_D_ID, O_ID))", "CREATE INDEX ORDERS_OID ON ORDERS(O_ID)", 82 "CREATE TABLE NEW_ORDER(\n" + " NO_O_ID INT NOT NULL,\n" + " NO_D_ID INT NOT NULL,\n" 83 + " NO_W_ID INT NOT NULL,\n" 84 + " PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID))", "CREATE TABLE ITEM(\n" + " I_ID INT NOT NULL,\n" + " I_IM_ID INT,\n" 88 + " I_NAME VARCHAR(24),\n" + " I_PRICE DECIMAL(5, 2),\n" 89 + " I_DATA VARCHAR(50),\n" + " PRIMARY KEY(I_ID))", 90 "CREATE TABLE STOCK(\n" + " S_I_ID INT NOT NULL,\n" + " S_W_ID INT NOT NULL,\n" 91 + " S_QUANTITY DECIMAL(4),\n" + " S_DIST_01 CHAR(24),\n" 92 + " S_DIST_02 CHAR(24),\n" + " S_DIST_03 CHAR(24),\n" 93 + " S_DIST_04 CHAR(24),\n" + " S_DIST_05 CHAR(24),\n" 94 + " S_DIST_06 CHAR(24),\n" + " S_DIST_07 CHAR(24),\n" 95 + " S_DIST_08 CHAR(24),\n" + " S_DIST_09 CHAR(24),\n" 96 + " S_DIST_10 CHAR(24),\n" + " S_YTD DECIMAL(8),\n" 97 + " S_ORDER_CNT DECIMAL(4),\n" 98 + " S_REMOTE_CNT DECIMAL(4),\n" + " S_DATA VARCHAR(50),\n" 99 + " PRIMARY KEY(S_W_ID, S_I_ID))", "CREATE TABLE ORDER_LINE(\n" 104 + " OL_O_ID INT NOT NULL,\n" 105 + " OL_D_ID INT NOT NULL,\n" 106 + " OL_W_ID INT NOT NULL,\n" 107 + " OL_NUMBER INT NOT NULL,\n" 108 + " OL_I_ID INT,\n" 109 + " OL_SUPPLY_W_ID INT,\n" 110 + " OL_DELIVERY_D TIMESTAMP,\n" 111 + " OL_QUANTITY DECIMAL(2),\n" 112 + " OL_AMOUNT DECIMAL(6, 2),\n" 113 + " OL_DIST_INFO CHAR(24),\n" 114 + " PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER))", "CREATE TABLE RESULTS(\n" + " ID INT NOT NULL PRIMARY KEY,\n" 120 + " TERMINAL INT,\n" + " OPERATION INT,\n" 121 + " RESPONSE_TIME INT,\n" + " PROCESSING_TIME INT,\n" 122 + " KEYING_TIME INT,\n" + " THINK_TIME INT,\n" 123 + " SUCCESSFULL INT,\n" + " NOW TIMESTAMP)" }; 124 125 public void init(Database db, int size) throws Exception { 126 this.db = db; 127 128 random = new BenchCRandom(); 129 130 items = size * 10; 131 warehouses = 1; 132 districtsPerWarehouse = Math.max(1, size / 100); 133 customersPerDistrict = Math.max(1, size / 100); 134 ordersPerDistrict = Math.max(1, size / 1000); 135 136 db.start(this, "Init"); 137 db.openConnection(); 138 load(); 139 db.commit(); 140 db.closeConnection(); 141 db.end(); 142 143 db.start(this, "Open/Close"); 144 db.openConnection(); 145 db.closeConnection(); 146 db.end(); 147 148 } 149 150 private void load() throws Exception { 151 for (int i = 0; i < TABLES.length; i++) { 152 db.dropTable(TABLES[i]); 153 } 154 for (int i = 0; i < CREATE_SQL.length; i++) { 155 db.update(CREATE_SQL[i]); 156 } 157 db.setAutoCommit(false); 158 loadItem(); 159 loadWarehouse(); 160 loadCustomer(); 161 loadOrder(); 162 db.commit(); 163 trace("load done"); 164 } 165 166 167 168 void trace(String s) { 169 action = s; 170 } 171 172 void trace(int i,int max) { 173 db.trace(action, i, max); 174 } 175 176 private void loadItem() throws Exception { 177 trace("load item"); 178 boolean[] original = random.getBoolean(items, items / 10); 179 PreparedStatement prep = db.prepare("INSERT INTO ITEM(I_ID, I_IM_ID, I_NAME, I_PRICE, I_DATA) " 180 + "VALUES(?, ?, ?, ?, ?)"); 181 for (int i_id = 1; i_id <= items; i_id++) { 182 String i_name = random.getString(14, 24); 183 BigDecimal i_price = random.getBigDecimal(random.getInt(100, 10000), 2); 184 String i_data = random.getString(26, 50); 185 if (original[i_id - 1]) { 186 i_data = random.replace(i_data, "original"); 187 } 188 prep.setInt(1, i_id); 189 prep.setInt(2, random.getInt(1, 10000)); 190 prep.setString(3, i_name); 191 prep.setBigDecimal(4, i_price); 192 prep.setString(5, i_data); 193 db.update(prep); 194 trace(i_id, items); 195 if(i_id%commitEvery==0) { 196 db.commit(); 197 } 198 } 199 } 200 201 private void loadWarehouse() throws Exception { 202 trace("loading warehouses"); 203 PreparedStatement prep = db.prepare("INSERT INTO WAREHOUSE(W_ID, W_NAME, W_STREET_1, " 204 + "W_STREET_2, W_CITY, W_STATE, W_ZIP, W_TAX, W_YTD) " 205 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"); 206 for (int w_id = 1; w_id <= warehouses; w_id++) { 207 String w_name = random.getString(6, 10); 208 String [] address = random.getAddress(); 209 String w_street_1 = address[0]; 210 String w_street_2 = address[1]; 211 String w_city = address[2]; 212 String w_state = address[3]; 213 String w_zip = address[4]; 214 BigDecimal w_tax = random.getBigDecimal(random.getInt(0, 2000), 4); 215 BigDecimal w_ytd = new BigDecimal("300000.00"); 216 prep.setInt(1, w_id); 217 prep.setString(2, w_name); 218 prep.setString(3, w_street_1); 219 prep.setString(4, w_street_2); 220 prep.setString(5, w_city); 221 prep.setString(6, w_state); 222 prep.setString(7, w_zip); 223 prep.setBigDecimal(8, w_tax); 224 prep.setBigDecimal(9, w_ytd); 225 db.update(prep); 226 loadStock(w_id); 227 loadDistrict(w_id); 228 if(w_id%commitEvery==0) { 229 db.commit(); 230 } 231 } 232 } 233 234 private void loadCustomer() throws Exception { 235 trace("load customers"); 236 int max = warehouses * districtsPerWarehouse; 237 int i = 0; 238 for (int w_id = 1; w_id <= warehouses; w_id++) { 239 for (int d_id = 1; d_id <= districtsPerWarehouse; d_id++) { 240 loadCustomerSub(d_id, w_id); 241 trace(i++, max); 242 if(i%commitEvery==0) { 243 db.commit(); 244 } 245 } 246 } 247 } 248 249 private void loadCustomerSub(int d_id, int w_id) throws Exception { 250 Timestamp timestamp = new Timestamp(System.currentTimeMillis()); 251 PreparedStatement prepCustomer = db.prepare("INSERT INTO CUSTOMER(C_ID, C_D_ID, C_W_ID, " 252 + "C_FIRST, C_MIDDLE, C_LAST, " 253 + "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, " 254 + "C_PHONE, C_SINCE, C_CREDIT, " 255 + "C_CREDIT_LIM, C_DISCOUNT, C_BALANCE, C_DATA, " 256 + "C_YTD_PAYMENT, C_PAYMENT_CNT, C_DELIVERY_CNT) " 257 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 258 PreparedStatement prepHistory = db.prepare("INSERT INTO HISTORY(H_C_ID, H_C_D_ID, H_C_W_ID, " 259 + "H_W_ID, H_D_ID, H_DATE, H_AMOUNT, H_DATA) " 260 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); 261 for (int c_id = 1; c_id <= customersPerDistrict; c_id++) { 262 int c_d_id = d_id; 263 int c_w_id = w_id; 264 String c_first = random.getString(8, 16); 265 String c_middle = "OE"; 266 String c_last; 267 if (c_id < 1000) { 268 c_last = random.getLastname(c_id); 269 } else { 270 c_last = random.getLastname(random.getNonUniform(255, 0, 999)); 271 } 272 String [] address = random.getAddress(); 273 String c_street_1 = address[0]; 274 String c_street_2 = address[1]; 275 String c_city = address[2]; 276 String c_state = address[3]; 277 String c_zip = address[4]; 278 String c_phone = random.getNumberString(16, 16); 279 String c_credit; 280 if (random.getInt(0, 1) == 0) { 281 c_credit = "GC"; 282 } else { 283 c_credit = "BC"; 284 } 285 BigDecimal c_discount = random.getBigDecimal(random.getInt(0, 5000), 4); 286 BigDecimal c_balance = new BigDecimal("-10.00"); 287 BigDecimal c_credit_lim = new BigDecimal("50000.00"); 288 String c_data = random.getString(300, 500); 289 BigDecimal c_ytd_payment = new BigDecimal("10.00"); 290 int c_payment_cnt = 1; 291 int c_delivery_cnt = 1; 292 prepCustomer.setInt(1, c_id); 293 prepCustomer.setInt(2, c_d_id); 294 prepCustomer.setInt(3, c_w_id); 295 prepCustomer.setString(4, c_first); 296 prepCustomer.setString(5, c_middle); 297 prepCustomer.setString(6, c_last); 298 prepCustomer.setString(7, c_street_1); 299 prepCustomer.setString(8, c_street_2); 300 prepCustomer.setString(9, c_city); 301 prepCustomer.setString(10, c_state); 302 prepCustomer.setString(11, c_zip); 303 prepCustomer.setString(12, c_phone); 304 prepCustomer.setTimestamp(13, timestamp); 305 prepCustomer.setString(14, c_credit); 306 prepCustomer.setBigDecimal(15, c_credit_lim); 307 prepCustomer.setBigDecimal(16, c_discount); 308 prepCustomer.setBigDecimal(17, c_balance); 309 prepCustomer.setString(18, c_data); 310 prepCustomer.setBigDecimal(19, c_ytd_payment); 311 prepCustomer.setInt(20, c_payment_cnt); 312 prepCustomer.setInt(21, c_delivery_cnt); 313 db.update(prepCustomer); 314 BigDecimal h_amount = new BigDecimal("10.00"); 315 String h_data = random.getString(12, 24); 316 prepHistory.setInt(1, c_id); 317 prepHistory.setInt(2, c_d_id); 318 prepHistory.setInt(3, c_w_id); 319 prepHistory.setInt(4, c_w_id); 320 prepHistory.setInt(5, c_d_id); 321 prepHistory.setTimestamp(6, timestamp); 322 prepHistory.setBigDecimal(7, h_amount); 323 prepHistory.setString(8, h_data); 324 db.update(prepHistory); 325 } 326 } 327 328 private void loadOrder() throws Exception { 329 trace("load orders"); 330 int max = warehouses * districtsPerWarehouse; 331 int i = 0; 332 for (int w_id = 1; w_id <= warehouses; w_id++) { 333 for (int d_id = 1; d_id <= districtsPerWarehouse; d_id++) { 334 loadOrderSub(d_id, w_id); 335 trace(i++, max); 336 } 337 } 338 } 339 340 private void loadOrderSub(int d_id, int w_id) throws Exception { 341 int o_d_id = d_id; 342 int o_w_id = w_id; 343 Timestamp timestamp = new Timestamp(System.currentTimeMillis()); 344 int[] orderid = random.getPermutation(ordersPerDistrict); 345 PreparedStatement prepOrder = db.prepare("INSERT INTO ORDERS(O_ID, O_C_ID, O_D_ID, O_W_ID, " 346 + "O_ENTRY_D, O_CARRIER_ID, O_OL_CNT, O_ALL_LOCAL) " 347 + "VALUES(?, ?, ?, ?, ?, ?, ?, 1)"); 348 PreparedStatement prepNewOrder = db.prepare("INSERT INTO NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID) " 349 + "VALUES (?, ?, ?)"); 350 PreparedStatement prepLine = db.prepare("INSERT INTO ORDER_LINE(" 351 + "OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, " 352 + "OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, " 353 + "OL_DIST_INFO, OL_DELIVERY_D)" 354 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)"); 355 for (int o_id = 1, i=0; o_id <= ordersPerDistrict; o_id++) { 356 int o_c_id = orderid[o_id - 1]; 357 int o_carrier_id = random.getInt(1, 10); 358 int o_ol_cnt = random.getInt(5, 15); 359 prepOrder.setInt(1, o_id); 360 prepOrder.setInt(2, o_c_id); 361 prepOrder.setInt(3, o_d_id); 362 prepOrder.setInt(4, o_w_id); 363 prepOrder.setTimestamp(5, timestamp); 364 prepOrder.setInt(7, o_ol_cnt); 365 if (o_id <= 2100) { 366 prepOrder.setInt(6, o_carrier_id); 367 } else { 368 prepOrder.setNull(6, Types.INTEGER); 370 prepNewOrder.setInt(1, o_id); 371 prepNewOrder.setInt(2, o_d_id); 372 prepNewOrder.setInt(3, o_w_id); 373 db.update(prepNewOrder); 374 } 375 db.update(prepOrder); 376 for (int ol = 1; ol <= o_ol_cnt; ol++) { 377 int ol_i_id = random.getInt(1, items); 378 int ol_supply_w_id = o_w_id; 379 int ol_quantity = 5; 380 String ol_dist_info = random.getString(24); 381 BigDecimal ol_amount; 382 if (o_id < 2101) { 383 ol_amount = random.getBigDecimal(0, 2); 384 } else { 385 ol_amount = random.getBigDecimal(random.getInt(0, 1000000), 2); 386 } 387 prepLine.setInt(1, o_id); 388 prepLine.setInt(2, o_d_id); 389 prepLine.setInt(3, o_w_id); 390 prepLine.setInt(4, ol); 391 prepLine.setInt(5, ol_i_id); 392 prepLine.setInt(6, ol_supply_w_id); 393 prepLine.setInt(7, ol_quantity); 394 prepLine.setBigDecimal(8, ol_amount); 395 prepLine.setString(9, ol_dist_info); 396 db.update(prepLine); 397 if(i++%commitEvery==0) { 398 db.commit(); 399 } 400 } 401 } 402 } 403 404 private void loadStock(int w_id) throws Exception { 405 trace("load stock (warehouse " + w_id + ")"); 406 int s_w_id = w_id; 407 boolean[] original = random.getBoolean(items, items / 10); 408 PreparedStatement prep = db.prepare("INSERT INTO STOCK(S_I_ID, S_W_ID, S_QUANTITY, " 409 + "S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, " 410 + "S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10, " 411 + "S_DATA, S_YTD, S_ORDER_CNT, S_REMOTE_CNT) " 412 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 413 for (int s_i_id = 1; s_i_id <= items; s_i_id++) { 414 int s_quantity = random.getInt(10, 100); 415 String s_dist_01 = random.getString(24); 416 String s_dist_02 = random.getString(24); 417 String s_dist_03 = random.getString(24); 418 String s_dist_04 = random.getString(24); 419 String s_dist_05 = random.getString(24); 420 String s_dist_06 = random.getString(24); 421 String s_dist_07 = random.getString(24); 422 String s_dist_08 = random.getString(24); 423 String s_dist_09 = random.getString(24); 424 String s_dist_10 = random.getString(24); 425 String s_data = random.getString(26, 50); 426 if (original[s_i_id - 1]) { 427 s_data = random.replace(s_data, "original"); 428 } 429 prep.setInt(1, s_i_id); 430 prep.setInt(2, s_w_id); 431 prep.setInt(3, s_quantity); 432 prep.setString(4, s_dist_01); 433 prep.setString(5, s_dist_02); 434 prep.setString(6, s_dist_03); 435 prep.setString(7, s_dist_04); 436 prep.setString(8, s_dist_05); 437 prep.setString(9, s_dist_06); 438 prep.setString(10, s_dist_07); 439 prep.setString(11, s_dist_08); 440 prep.setString(12, s_dist_09); 441 prep.setString(13, s_dist_10); 442 prep.setString(14, s_data); 443 prep.setInt(15, 0); 444 prep.setInt(16, 0); 445 prep.setInt(17, 0); 446 db.update(prep); 447 if(s_i_id%commitEvery==0) { 448 db.commit(); 449 } 450 trace(s_i_id, items); 451 } 452 } 453 454 private void loadDistrict(int w_id) throws Exception { 455 int d_w_id = w_id; 456 BigDecimal d_ytd = new BigDecimal("300000.00"); 457 int d_next_o_id = 3001; 458 PreparedStatement prep = db.prepare("INSERT INTO DISTRICT(D_ID, D_W_ID, D_NAME, " 459 + "D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, " 460 + "D_TAX, D_YTD, D_NEXT_O_ID) " 461 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 462 for (int d_id = 1; d_id <= districtsPerWarehouse; d_id++) { 463 String d_name = random.getString(6, 10); 464 String [] address = random.getAddress(); 465 String d_street_1 = address[0]; 466 String d_street_2 = address[1]; 467 String d_city = address[2]; 468 String d_state = address[3]; 469 String d_zip = address[4]; 470 BigDecimal d_tax = random.getBigDecimal(random.getInt(0, 2000), 4); 471 prep.setInt(1, d_id); 472 prep.setInt(2, d_w_id); 473 prep.setString(3, d_name); 474 prep.setString(4, d_street_1); 475 prep.setString(5, d_street_2); 476 prep.setString(6, d_city); 477 prep.setString(7, d_state); 478 prep.setString(8, d_zip); 479 prep.setBigDecimal(9, d_tax); 480 prep.setBigDecimal(10, d_ytd); 481 prep.setInt(11, d_next_o_id); 482 db.update(prep); 483 trace(d_id, districtsPerWarehouse); 484 } 485 } 486 487 public void run() throws Exception { 488 db.start(this, "Transactions"); 489 db.openConnection(); 490 for(int i=0; i<70; i++) { 491 BenchCThread process = new BenchCThread(db, this, random, i); 492 process.process(); 493 } 494 db.closeConnection(); 495 db.end(); 496 497 db.openConnection(); 498 BenchCThread process = new BenchCThread(db, this, random, 0); 499 process.process(); 500 db.logMemory(this, "Memory Usage"); 501 db.closeConnection(); 502 } 503 504 public String getName() { 505 return "BenchC"; 506 } 507 508 } 509 510 | Popular Tags |