loaddata.java

业界最为经典的SQL性能测试工具

        String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
        System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
        lastTimeMS = tmpTime;

        if (outputFiles == false)
        {
          itemPrepStmt.executeBatch();
	    }
        transCommit();
        now = new java.util.Date();
        System.out.println("End Item Load @  " + now);

      } catch(SQLException se) {
        System.out.println(se.getMessage());
        transRollback();
      } catch(Exception e) {
        e.printStackTrace();
        transRollback();
      }

      return(k);

} // end loadItem()



  static int loadWhse(int whseKount) {

      try {

        now = new java.util.Date();
        System.out.println("\nStart Whse Load for " + whseKount + " Whses @ " + now + " ...");

        if (outputFiles == true)
        {
            out = new PrintWriter(new FileOutputStream(fileLocation + "warehouse.csv"));
            System.out.println("\nWriting Warehouse file to: " + fileLocation + "warehouse.csv");
        }

        Warehouse warehouse  = new Warehouse();
        for (int i=1; i <= whseKount; i++) {

          warehouse.w_id       = i;
          warehouse.w_ytd      = 300000;

          // random within [0.0000 .. 0.2000]
          warehouse.w_tax = (float)((jTPCCUtil.randomNumber(0,2000,gen))/10000.0);

          warehouse.w_name     = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(6,10,gen));
          warehouse.w_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
          warehouse.w_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
          warehouse.w_city     = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
          warehouse.w_state    = jTPCCUtil.randomStr(3).toUpperCase();
          warehouse.w_zip      = "123456789";

          if (outputFiles == false)
          {
            whsePrepStmt.setLong(1, warehouse.w_id);
            whsePrepStmt.setDouble(2, warehouse.w_ytd);
            whsePrepStmt.setDouble(3, warehouse.w_tax);
            whsePrepStmt.setString(4, warehouse.w_name);
            whsePrepStmt.setString(5, warehouse.w_street_1);
            whsePrepStmt.setString(6, warehouse.w_street_2);
            whsePrepStmt.setString(7, warehouse.w_city);
            whsePrepStmt.setString(8, warehouse.w_state);
            whsePrepStmt.setString(9, warehouse.w_zip);
            whsePrepStmt.executeUpdate();
	      } else {
			String str = "";
            str = str + warehouse.w_id + ",";
            str = str + warehouse.w_ytd + ",";
            str = str + warehouse.w_tax + ",";
            str = str + warehouse.w_name + ",";
            str = str + warehouse.w_street_1 + ",";
            str = str + warehouse.w_street_2 + ",";
            str = str + warehouse.w_city + ",";
            str = str + warehouse.w_state + ",";
            str = str + warehouse.w_zip;
    		out.println(str);
	      }

        } // end for

        transCommit();
        now = new java.util.Date();

        long tmpTime = new java.util.Date().getTime();
        System.out.println("Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000));
        lastTimeMS = tmpTime;
        System.out.println("End Whse Load @  " + now);

      } catch(SQLException se) {
        System.out.println(se.getMessage());
        transRollback();
      } catch(Exception e) {
        e.printStackTrace();
        transRollback();
      }

      return (whseKount);

  } // end loadWhse()



  static int loadStock(int whseKount, int itemKount) {

      int k = 0;
      int t = 0;
      int randPct = 0;
      int len = 0;
      int startORIGINAL = 0;

      try {

        now = new java.util.Date();
        t = (whseKount * itemKount);
        System.out.println("\nStart Stock Load for " + t + " units @ " + now + " ...");

        if (outputFiles == true)
        {
            out = new PrintWriter(new FileOutputStream(fileLocation + "stock.csv"));
            System.out.println("\nWriting Stock file to: " + fileLocation + "stock.csv");
        }

        Stock stock  = new Stock();

        for (int i=1; i <= itemKount; i++) {

          for (int w=1; w <= whseKount; w++) {

            stock.s_i_id = i;
            stock.s_w_id = w;
            stock.s_quantity = jTPCCUtil.randomNumber(10, 100, gen);
            stock.s_ytd = 0;
            stock.s_order_cnt = 0;
            stock.s_remote_cnt = 0;

            // s_data
            randPct = jTPCCUtil.randomNumber(1, 100, gen);
            len = jTPCCUtil.randomNumber(26, 50, gen);
            if ( randPct > 10 ) {
               // 90% of time i_data isa random string of length [26 .. 50]
               stock.s_data = jTPCCUtil.randomStr(len);
            } else {
              // 10% of time i_data has "ORIGINAL" crammed somewhere in middle
              startORIGINAL = jTPCCUtil.randomNumber(2, (len - 8), gen);
              stock.s_data =
                jTPCCUtil.randomStr(startORIGINAL - 1) +
                "ORIGINAL" +
                jTPCCUtil.randomStr(len - startORIGINAL - 9);
            }

            stock.s_dist_01 = jTPCCUtil.randomStr(24);
            stock.s_dist_02 = jTPCCUtil.randomStr(24);
            stock.s_dist_03 = jTPCCUtil.randomStr(24);
            stock.s_dist_04 = jTPCCUtil.randomStr(24);
            stock.s_dist_05 = jTPCCUtil.randomStr(24);
            stock.s_dist_06 = jTPCCUtil.randomStr(24);
            stock.s_dist_07 = jTPCCUtil.randomStr(24);
            stock.s_dist_08 = jTPCCUtil.randomStr(24);
            stock.s_dist_09 = jTPCCUtil.randomStr(24);
            stock.s_dist_10 = jTPCCUtil.randomStr(24);

          k++;
          if (outputFiles == false)
          {
              stckPrepStmt.setLong(1, stock.s_i_id);
              stckPrepStmt.setLong(2, stock.s_w_id);
              stckPrepStmt.setDouble(3, stock.s_quantity);
              stckPrepStmt.setDouble(4, stock.s_ytd);
              stckPrepStmt.setLong(5, stock.s_order_cnt);
              stckPrepStmt.setLong(6, stock.s_remote_cnt);
              stckPrepStmt.setString(7, stock.s_data);
              stckPrepStmt.setString(8, stock.s_dist_01);
              stckPrepStmt.setString(9, stock.s_dist_02);
              stckPrepStmt.setString(10, stock.s_dist_03);
              stckPrepStmt.setString(11, stock.s_dist_04);
              stckPrepStmt.setString(12, stock.s_dist_05);
              stckPrepStmt.setString(13, stock.s_dist_06);
              stckPrepStmt.setString(14, stock.s_dist_07);
              stckPrepStmt.setString(15, stock.s_dist_08);
              stckPrepStmt.setString(16, stock.s_dist_09);
              stckPrepStmt.setString(17, stock.s_dist_10);
              stckPrepStmt.addBatch();
            if (( k % configCommitCount) == 0) {
              long tmpTime = new java.util.Date().getTime();
              String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
              System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
              lastTimeMS = tmpTime;
              stckPrepStmt.executeBatch();
              stckPrepStmt.clearBatch();
              transCommit();
            }
	      } else {
			String str = "";
              str = str + stock.s_i_id + ",";
              str = str + stock.s_w_id + ",";
              str = str + stock.s_quantity + ",";
              str = str + stock.s_ytd + ",";
              str = str + stock.s_order_cnt + ",";
              str = str + stock.s_remote_cnt + ",";
              str = str + stock.s_data + ",";
              str = str + stock.s_dist_01 + ",";
              str = str + stock.s_dist_02 + ",";
              str = str + stock.s_dist_03 + ",";
              str = str + stock.s_dist_04 + ",";
              str = str + stock.s_dist_05 + ",";
              str = str + stock.s_dist_06 + ",";
              str = str + stock.s_dist_07 + ",";
              str = str + stock.s_dist_08 + ",";
              str = str + stock.s_dist_09 + ",";
              str = str + stock.s_dist_10;
              out.println(str);

            if (( k % configCommitCount) == 0) {
              long tmpTime = new java.util.Date().getTime();
              String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
              System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
              lastTimeMS = tmpTime;
		      }
           }

          } // end for [w]

        } // end for [i]


        long tmpTime = new java.util.Date().getTime();
        String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
        System.out.println(etStr.substring(0, 30) + "  Writing final records " + k + " of " + t);
        lastTimeMS = tmpTime;
        if (outputFiles == false)
        {
          stckPrepStmt.executeBatch();
	    }
        transCommit();

        now = new java.util.Date();
        System.out.println("End Stock Load @  " + now);

      } catch(SQLException se) {
        System.out.println(se.getMessage());
        transRollback();

      } catch(Exception e) {
        e.printStackTrace();
        transRollback();
      }

      return (k);

  } // end loadStock()



  static int loadDist(int whseKount, int distWhseKount) {

      int k = 0;
      int t = 0;

      try {

        now = new java.util.Date();

        if (outputFiles == true)
        {
            out = new PrintWriter(new FileOutputStream(fileLocation + "district.csv"));
            System.out.println("\nWriting District file to: " + fileLocation + "district.csv");
        }

        District district  = new District();

        t = (whseKount * distWhseKount);
        System.out.println("\nStart District Data for " + t + " Dists @ " + now + " ...");

        for (int w=1; w <= whseKount; w++) {

          for (int d=1; d <= distWhseKount; d++) {

            district.d_id = d;
            district.d_w_id = w;
            district.d_ytd = 30000;

            // random within [0.0000 .. 0.2000]
            district.d_tax = (float)((jTPCCUtil.randomNumber(0,2000,gen))/10000.0);

            district.d_next_o_id = 3001;
            district.d_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(6,10,gen));
            district.d_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
            district.d_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
            district.d_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10,20,gen));
            district.d_state = jTPCCUtil.randomStr(3).toUpperCase();
            district.d_zip = "123456789";

          k++;
          if (outputFiles == false)
          {
              distPrepStmt.setLong(1, district.d_id);
              distPrepStmt.setLong(2, district.d_w_id);
              distPrepStmt.setDouble(3, district.d_ytd);
              distPrepStmt.setDouble(4, district.d_tax);
              distPrepStmt.setLong(5, district.d_next_o_id);
              distPrepStmt.setString(6, district.d_name);
              distPrepStmt.setString(7, district.d_street_1);
              distPrepStmt.setString(8, district.d_street_2);
              distPrepStmt.setString(9, district.d_city);
              distPrepStmt.setString(10, district.d_state);
              distPrepStmt.setString(11, district.d_zip);
              distPrepStmt.executeUpdate();
	      } else {
              String str = "";
              str = str + district.d_id + ",";
              str = str + district.d_w_id + ",";
              str = str + district.d_ytd + ",";
              str = str + district.d_tax + ",";
              str = str + district.d_next_o_id + ",";
              str = str + district.d_name + ",";
              str = str + district.d_street_1 + ",";
              str = str + district.d_street_2 + ",";
              str = str + district.d_city + ",";
              str = str + district.d_state + ",";
              str = str + district.d_zip;
              out.println(str);
          }

          } // end for [d]

        } // end for [w]

        long tmpTime = new java.util.Date().getTime();
        String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
        System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
        lastTimeMS = tmpTime;
        transCommit();
        now = new java.util.Date();
        System.out.println("End District Load @  " + now);

      } catch(SQLException se) {
        System.out.println(se.getMessage());
        transRollback();
      } catch(Exception e) {
        e.printStackTrace();
        transRollback();
      }

      return (k);

  } // end loadDist()



  static int loadCust(int whseKount, int distWhseKount, int custDistKount) {

      int k = 0;
      int t = 0;
      double cCreditLim = 0;

      Customer customer  = new Customer();
      History history = new History();
      PrintWriter outHist = null;


      try {

        now = new java.util.Date();

        if (outputFiles == true)
        {
            out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv"));
            System.out.println("\nWriting Customer file to: " + fileLocation + "customer.csv");
            outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv"));
            System.out.println("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv");
        }

        t = (whseKount * distWhseKount * custDistKount * 2);
        System.out.println("\nStart Cust-Hist Load for " +
           t + " Cust-Hists @ " + now + " ...");