SQL Server應用一個語句塊批量拔出多筆記錄的三種辦法。本站提示廣大學習愛好者:(SQL Server應用一個語句塊批量拔出多筆記錄的三種辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是SQL Server應用一個語句塊批量拔出多筆記錄的三種辦法正文
tpch是TPC(Transaction Processing Performance Council)組織供給的對象包。用於停止OLAP測試,以評價貿易剖析中決議計劃支撐體系(DSS)的機能。它包括了一整套面向貿易的ad-hoc查詢和並發數據修正,強調測試的是數據庫、平台和I/O機能,存眷查詢才能。
官網:http://www.tpc.org/tpch
下載地址:http://www.tpc.org/tpch/spec/tpch_2_14_3.tgz 或 http://www.tpc.org/tpch/spec/tpch_2_14_3.zip
1、編譯裝置
下載源碼包,解緊縮,然後:
cp makefile.suite makefile
修正makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等界說:
################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC = gcc
# Current values for DATABASE are: INFORMIX, DB2, ORACLE,
# SQLSERVER, SYBASE, TDAT (Teradata)
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
修正tpcd.h文件,增長幾行宏界說:
#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif
然後履行make編譯,編譯終了後會生成兩個可履行文件:
dbgen:數據生成對象。在應用InfiniDB官方測試劇本停止測試時,須要用該對象生成tpch相干表數據。
qgen:SQL生成對象
生成初始化測試數據:
[root@imysql tpch]# time ./dbgen -s 50
TPC-H Population Generator (Version 2.9.0)
Copyright Transaction Processing Performance Council 1994 - 2008
real 192m43.897s
user 37m45.398s
sys 19m4.132s
[root@imysql tpch]# ls -lh *tbl
-rw-r--r-- 1 root root 1.2G Sep 21 15:23 customer.tbl
-rw-r--r-- 1 root root 1.4G Sep 21 15:23 lineitem.tbl
-rw-r--r-- 1 root root 2.2K Sep 21 15:23 nation.tbl
-rw-r--r-- 1 root root 317M Sep 21 15:23 orders.tbl
-rw-r--r-- 1 root root 504K Sep 21 15:23 partsupp.tbl
-rw-r--r-- 1 root root 464K Sep 21 15:23 part.tbl
-rw-r--r-- 1 root root 389 Sep 21 15:23 region.tbl
-rw-r--r-- 1 root root 69M Sep 21 15:23 supplier.tbl
dbgen參數 -s 的感化是指定生成測試數據的倉庫數,建議基准值設定在100以上,在我的測試情況中,普通都設定為1000。
因為源碼包中自帶的tpch初始化庫表劇本其實不能完整實用MySQL,須要修正部門代碼。
師長教師成測試SQL劇本:
[root@imysql tpch]# ./qgen | sed -e 's/\r//' > queries/tpch_queries.sql
爾後用vim翻開tpch_queries.sql劇本,停止上面幾回全局調換:
:%s/;\nlimit/ limit/g
:%s/limit -1/limit 1/g
搜刮一切相似上面的語句,去失落前面的 (3):
l_shipdate <= date '1998-12-01' - interval '106' day (3)
=>
l_shipdate <= date '1998-12-01' - interval '106' day
再修正第369行鄰近:
count(o_orderkey)
=>
count(o_orderkey) as c_count
修正第376行閣下
) as c_orders (c_custkey, c_count)
=>
) as c_orders
修正第431行鄰近:
drop view revenue0 limit 1;
=>
drop view revenue0;
最初把年夜的查詢SQL劇本拆分紅23個自力的SQL查詢劇本,分離從tpch_01.sql ~ tpch_23.sql。
2、初始化庫表
tpch供給的數據庫表初始化劇本有些小成績,須要停止修正:
dss.ddl – DSS庫初始化DDL劇本
dss.ri – DSS數據表創立索引、外鍵劇本
dss.ddl劇本須要增長幾行:
drop database tpch;
create database tpch;
use tpch;
dss.ri劇本須要修正幾個處所:
修正第4行閣下:
CONNECT TO TPCD;
=>
Use tpch;
修正第6~13行,一切的SQL正文符 “--” 前面再加一個空格:
-- ALTER TABLE TPCD.REGION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.NATION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PART DROP PRIMARY KEY;
-- ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;
-- ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;
-- ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;
-- ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;
修正第25行:
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION;
=>
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);
修正第40行:
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION;
=>
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION(N_NATIONKEY);
修正第55行:
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION;
=>
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION(N_NATIONKEY);
修正第73行:
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER;
=>
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER(S_SUPPKEY);
修正第78行:
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART;
=>
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART(P_PARTKEY);
修正第84行:
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER;
=>
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER(C_CUSTKEY);
修正第90行:
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCD.ORDERS;
=>
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCD.ORDERS(O_ORDERKEY);
修正第96行:
TPCD.PARTSUPP;
=>
TPCD.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
別的,因為tpch生成的表名是年夜寫的,須要修正下表名成小寫的,是以再增長幾行:
use tpch;
alter table CUSTOMER rename to customer ;
alter table LINEITEM rename to lineitem ;
alter table NATION rename to nation ;
alter table ORDERS rename to orders ;
alter table PART rename to part ;
alter table PARTSUPP rename to partsupp ;
alter table REGION rename to region ;
alter table SUPPLIER rename to supplier ;
3、導入數據
測試數據生成了,測試庫表也初始化完了,接上去便可以開端導入數據了。
須要留意下,假如開啟了binlog,在導入前最好先封閉binlog,不然會提醒超越max_binlog_cache_size的毛病提醒,假如不克不及封閉binlog,則須要把導入文件切分紅多個小文件再導入。
myqsl -e "LOAD DATA INFILE 'path/dbgen/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';"
myqsl -e "LOAD DATA INFILE 'path/dbgen/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';"
4、履行tpch測試
接上去便可以停止tpch測試了,逐一履行23個查詢SQL劇本便可,每次履行前都要重啟下MySQL實例,確保每次的內存緩沖區都是清潔的。
簡略輪回測試劇本以下:
#!/bin/sh ## ## 履行tpch OLAP測試 ## ## writed by yejr(http://imysql.com), 2012/12/14 ## PATH=$PATH:/usr/local/bin export PATH . ~/.bash_profile > /dev/null 2>&1 exec 3>&1 4>&2 1>> tpch-benchmark-olap-`date +'%Y%m%d%H%M%S'`.log 2>&1 I=1 II=3 while [ $I -le $II ] do N=1 T=23 while [ $N -lt $T ] do if [ $N -lt 10 ] ; then NN='0'$N else NN=$N fi echo "query $NN starting" /etc/init.d/mysql restart time mysql -f tpch < ./queries/tpch_${NN}.sql echo "query $NN ended!" N=`expr $N + 1` done I=`expr $I + 1` Done
附件:tpch初始化、主動化測試劇本緊縮包與word手冊。