/*
Purpose: 使用 Global Variable 提升 Dynamic SQL 的 performance
建立兩個不同的 procedures 比較使用或不使用 Global Variable 的效能差異
參考: Expert One-on-One (Thomas Kyte)
topic〔Use Bind Variables〕
*/
/* Connect to ORIONDB */
C:\>db2 connect to oriondb
資料庫連線資訊
資料庫伺服器 = DB2/NT 9.5.0
SQL 授權 ID = ORION
本地資料庫別名 = ORIONDB
/* Create Globa Variable */
C:\>db2 create variable orion.keyvalue integer
DB20000I SQL 指令已順利完成。
/* 建立測試 Table Target_tab */
create table target_tab
(
col1 integer,
col2 varchar(64),
col3 varchar(64)
) in userspace1
/* 建立測試 Table Source_tab */
create table source_tab
(
col1 integer,
col2 varchar(64),
col3 varchar(64)
) in userspace1
/* 新增測試資料 */
insert into source_tab
select row_number() over(),tbname,name
from sysibm.syscolumns;
insert into source_tab
select (select max(col1) + 1 from source_tab),
tbname,name
from sysibm.syscolumns;
insert into source_tab
select (select max(col1) + 1 from source_tab),
tbname,name
from sysibm.syscolumns;
insert into source_tab
select (select max(col1) + 1 from source_tab),
tbname,name
from sysibm.syscolumns;
select count(*) from source_tab
1
-----------
19804
已選取 1 個記錄。
/*
Create Procedure sp_testvariable (使用 Global Variable:KeyValue)
*/
CREATE PROCEDURE SP_TESTVARIABLE
SPECIFIC SP_TESTVARIABLE
LANGUAGE SQL
BEGIN
DECLARE n INTEGER;
DECLARE i INTEGER;
DECLARE strSql VARCHAR(128);
SELECT COUNT(*) INTO n
FROM SOURCE_TAB;
SET i = 1;
WHILE i <= n DO
-- 使用剛才建立的 Global Variable
SET KeyValue = i;
SET strSql = 'INSERT INTO TARGET_TAB ' ||
'SELECT * FROM SOURCE_TAB ' ||
'WHERE COL1 = KeyValue';
EXECUTE IMMEDIATE strSqL;
SET i = i + 1;
END WHILE;
END
/*
Create Procedure sp_testnovariable (不使用 Global Variable)
*/
CREATE PROCEDURE SP_TESTNOVARIABLE
SPECIFIC SP_TESTNOVARIABLE
LANGUAGE SQL
BEGIN
DECLARE n INTEGER;
DECLARE i INTEGER;
DECLARE strSql VARCHAR(128);
SELECT COUNT(*) INTO n
FROM SOURCE_TAB;
SET i = 1;
WHILE i <= n DO
SET strSql = 'INSERT INTO TARGET_TAB ' ||
'SELECT * FROM SOURCE_TAB ' ||
'WHERE COL1 = ' || CHAR(i);
EXECUTE IMMEDIATE strSqL;
SET i = i + 1;
END WHILE;
END
/* 編寫 CLP Script: test.sql 內容如下 */
alter table target_tab activate not logged initially with empty table;
values current timestamp;
call sp_testvariable;
values current timestamp;
/* 編寫 CLP Script: testno.sql 內容如下 */
alter table target_tab activate not logged initially with empty table;
values current timestamp;
call sp_testnovariable;
values current timestamp;
/* 執行 test.sql (使用Global Variable) */
C:\>db2 -tf test.sql
DB20000I SQL 指令已順利完成。
1
--------------------------
2008-10-01-14.12.39.334000
已選取 1 個記錄。
傳回狀態 = 0
1
--------------------------
2008-10-01-14.13.55.324000
已選取 1 個記錄。
C:\>db2 select count(*) from target_tab
1
-----------
19804
已選取 1 個記錄。
/* 計算執行時間(秒) */
SELECT TIMESTAMPDIFF(2,CHAR(TIMESTAMP('2008-10-01-14.13.55.324000')-
TIMESTAMP('2008-10-01-14.12.39.334000'))) SEC
FROM SYSIBM.SYSDUMMY1
SEC
-------------
75
已選取 1 個記錄。
/* 執行 testno.sql(不使用Global Variable) */
C:\>db2 -tf testno.sql
DB20000I SQL 指令已順利完成。
1
--------------------------
2008-10-01-14.14.29.964000
已選取 1 個記錄。
傳回狀態 = 0
1
--------------------------
2008-10-01-14.16.50.446000
已選取 1 個記錄。
/* 計算執行時間(秒) */
SELECT TIMESTAMPDIFF(2,CHAR(TIMESTAMP('2008-10-01-14.16.50.446000')-
TIMESTAMP('2008-10-01-14.14.29.964000'))) SEC
FROM SYSIBM.SYSDUMMY1
SEC
--------------
140
已選取 1 個記錄。
/* 不使用Global Variable 的 Dynamic SQL 執行效率慢了將近一倍的時間 */
