有关临时表
临时表本身很占开销：建立数据备份、以及临时表创建索引，都是开销较大的。其优点是对数据的规整、拼接功能强大。在利用临时表的时候，需要取长补短，避免由于使用不当引起的性能问题。
下示例，业务是“只存放回报中存在的客户、交易编码的上日持仓记录”，原来的实现是：
1、 创建临时表 create table tempdb..file_seat_done ( jour_no numeric(10,0) identity, -- 处理编号 tx_date char(8), -- 成交日期 exch_done_no varchar(16), -- 交易所成交序号 exch_code char, -- 交易所 seat_no varchar(10), -- 席位号 cust_no varchar(16), -- 客户号 cust_name varchar(8), -- 客户名称 20061222 6.4.0.2 tx_no varchar(16), -- 交易编码 vari_code varchar(8), -- 品种 deliv_date char(8), -- 交割期 done_price numeric(19,4), -- 成交价格 done_qty int, -- 成交手数 bs_flag char, -- 买卖标志 sh_flag char, -- 投保标志 eo_flag char, -- 开平标志 done_time char(8), -- 成交时间 note varchar(80), -- 导入时出错信息 err_flag char(1), -- 错误单标志 type_flag char(1), -- 强减标志 sys_no varchar(16), --系统号 20070423 6.4.0.3 file_id int, -- jq 20090107 增加文件编号字段 order_way char(1) null, --下单方式 20090508 6.8.0.1 order_id varchar(14) null, --报单号 20100804 6.8.4.1 exch_seat_no varchar(14) null --交易所席位号 20100804 6.8.4.1 ) 2、 插入临时表数据 insert into tempdb..file_seat_done select tx_date , -- 成交日期 case eo_flag when '0' then min(exch_done_no) -- 开仓则取最小成交序号 else max(exch_done_no) end as exch_done_no, -- 交易所成交序号 exch_code , -- 交易所 seat_no , -- 席位号 cust_no , -- 客户号 '' , -- 客户名称 20061222 6.4.0.2 tx_no , -- 交易编码 vari_code , -- 品种 deliv_date , -- 交割期 done_price , -- 成交价格 sum(done_qty) as done_qty , -- 成交手数 bs_flag , -- 买卖标志 sh_flag , -- 投保标志 eo_flag , -- 开平标志 case eo_flag when '0' then min(done_time) -- 开仓则取最小成交时间 else max(done_time) end as done_time, -- 成交时间 note, -- 导入时出错信息 err_flag, -- 错误单标志 type_flag, -- 强减标志 sys_no, --系统号 20070423 6.4.0.3 file_id, -- jq 20090107 增加文件编号字段 order_way, --下单方式 20090508 6.8.0.1 case eo_flag when '0' then min(order_id) else max(order_id) end as order_id, --报单号 20100804 6.8.4.1 case eo_flag when '0' then min(exch_seat_no) else max(exch_seat_no) end as exch_seat_no --交易所席位号 20100804 6.8.4.1 from tempdb..temp_seat_done where err_flag = '0' group by tx_date, exch_code, seat_no, cust_no, tx_no, vari_code, deliv_date, done_price, bs_flag, sh_flag,eo_flag, note,err_flag, type_flag, order_way --下单方式 order by tx_date, eo_flag,exch_code,seat_no,bs_flag,cust_no,vari_code,deliv_date,exch_done_no 3、 具体业务：只存放回报中存在的客户、交易编码的上日持仓记录 -- 只存放回报中存在的客户、交易编码的上日持仓记录 insert into file_cust_hold select a.tx_date , -- 成交日期 a.exch_code, -- 交易所 a.seat_no , -- 席位号 a.cust_no , -- 客户号 a.tx_no , -- 交易编码 a.vari_code, -- 品种 a.deliv_date, -- 交割期 sum(a.open_hand) as hold_qty, -- 持仓手数 a.bs_flag , -- 买卖标志 a.sh_flag , -- 投保标志 '1' as yt_flag -- 今仓昨仓标志('1':昨仓,'3':今仓) from cust_hold a where a.tx_date = (select b.max_date from tempdb..seat_maxdate b where a.exch_code = b.exch_code and a.seat_no = b.seat_no) and exists (select * from tempdb..file_seat_done c where a.exch_code = c.exch_code and a.seat_no = c.seat_no and a.cust_no = c.cust_no and a.vari_code = c.vari_code and a.deliv_date = c.deliv_date) group by a.tx_date, a.exch_code, a.seat_no, a.cust_no, a.tx_no, a.vari_code, a.deliv_date, a.bs_flag, a.sh_flag order by a.cust_no, a.tx_no, a.vari_code, a.deliv_date, a.bs_flag, a.sh_flag
优化后：
1、 创建临时表时，增加一个“上日结算日”字段 create table tempdb..file_seat_done ( jour_no numeric(10,0) identity, -- 处理编号 tx_date char(8), -- 成交日期 exch_done_no varchar(16), -- 交易所成交序号 exch_code char, -- 交易所 seat_no varchar(10), -- 席位号 cust_no varchar(16), -- 客户号 cust_name varchar(8), -- 客户名称 20061222 6.4.0.2 tx_no varchar(16), -- 交易编码 vari_code varchar(8), -- 品种 deliv_date char(8), -- 交割期 done_price numeric(19,4), -- 成交价格 done_qty int, -- 成交手数 bs_flag char, -- 买卖标志 sh_flag char, -- 投保标志 eo_flag char, -- 开平标志 done_time char(8), -- 成交时间 note varchar(80), -- 导入时出错信息 err_flag char(1), -- 错误单标志 type_flag char(1), -- 强减标志 sys_no varchar(16), --系统号 20070423 6.4.0.3 file_id int, -- jq 20090107 增加文件编号字段 order_way char(1) null, --下单方式 20090508 6.8.0.1 order_id varchar(14) null, --报单号 20100804 6.8.4.1 exch_seat_no varchar(14) null, --交易所席位号 20100804 6.8.4.1 last_settle_date varchar(8) -- jq 20120702 优化效率，增加上日结算日字段，关联的时候可以少关联一张表 ) 2、 插入临时表，对于新增字段来说，这次插入并没有源数据，所以填空 insert into tempdb..file_seat_done select tx_date , -- 成交日期 case eo_flag when '0' then min(exch_done_no) -- 开仓则取最小成交序号 else max(exch_done_no) end as exch_done_no, -- 交易所成交序号 exch_code , -- 交易所 seat_no , -- 席位号 cust_no , -- 客户号 '' , -- 客户名称 20061222 6.4.0.2 tx_no , -- 交易编码 vari_code , -- 品种 deliv_date , -- 交割期 done_price , -- 成交价格 sum(done_qty) as done_qty , -- 成交手数 bs_flag , -- 买卖标志 sh_flag , -- 投保标志 eo_flag , -- 开平标志 case eo_flag when '0' then min(done_time) -- 开仓则取最小成交时间 else max(done_time) end as done_time, -- 成交时间 note, -- 导入时出错信息 err_flag, -- 错误单标志 type_flag, -- 强减标志 sys_no, --系统号 20070423 6.4.0.3 file_id, -- jq 20090107 增加文件编号字段 order_way, --下单方式 20090508 6.8.0.1 case eo_flag when '0' then min(order_id) else max(order_id) end as order_id, --报单号 20100804 6.8.4.1 case eo_flag when '0' then min(exch_seat_no) else max(exch_seat_no) end as exch_seat_no, --交易所席位号 20100804 6.8.4.1 '' -- jq 20120702 补上默认值 from tempdb..temp_seat_done where err_flag = '0' group by tx_date, exch_code, seat_no, cust_no, tx_no, vari_code, deliv_date, done_price, bs_flag, sh_flag,eo_flag, note,err_flag, type_flag, order_way --下单方式 20090508 6.8.0.1 order by tx_date, eo_flag,exch_code,seat_no,bs_flag,cust_no,vari_code,deliv_date,exch_done_no 3、 创建索引，为下面的表关联作准备 create index tmp_i_0 on tempdb..file_seat_done (exch_code,seat_no,cust_no,vari_code,deliv_date) -- jq 20120702 为优化增加索引 4、 将tempdb..file_seat_done的“上日结算日”字段更新正确 update tempdb..file_seat_done set a.last_settle_date = b.max_date from tempdb..file_seat_done a,tempdb..seat_maxdate b where a.exch_code = b.exch_code and a.seat_no = b.seat_no 5、 为关联方便，在创建第2个索引 create index tmp_i_2 on tempdb..file_seat_done (last_settle_date,exch_code,seat_no,cust_no,vari_code,deliv_date) 6、 2表关联，获得业务所需的持仓：只存放回报中存在的客户、交易编码的上日持仓记录 insert into file_cust_hold select a.tx_date , -- 成交日期 a.exch_code , -- 交易所 a.seat_no , -- 席位号 a.cust_no , -- 客户号 a.tx_no , -- 交易编码 a.vari_code , -- 品种 a.deliv_date , -- 交割期 sum(a.open_hand) as hold_qty, -- 持仓手数 a.bs_flag , -- 买卖标志 a.sh_flag , -- 投保标志 '1' as yt_flag -- 今仓昨仓标志('1':昨仓,'3':今仓) from cust_hold a,tempdb..file_seat_done c where a.tx_date = c.last_settle_date and a.exch_code = c.exch_code and a.seat_no = c.seat_no and a.cust_no = c.cust_no and a.vari_code = c.vari_code and a.deliv_date = c.deliv_date group by a.tx_date, a.exch_code, a.seat_no, a.cust_no, a.tx_no, a.vari_code, a.deliv_date, a.bs_flag, a.sh_flag order by a.cust_no, a.tx_no, a.vari_code, a.deliv_date, a.bs_flag, a.sh_flag
改进之后的方法，主要改进了两点：
1、 改3表关联为2表关联
2、 2表关联的时候，总是能使用到索引
