导航

【摘】DB2程序性能

Posted on 2012-12-06 10:29  菜鸟都不容易  阅读(1127)  评论(0)  编辑  收藏  举报

【引】感谢原博主

http://blog.163.com/gz_xuhaoz/blog/static/32689092006124541150/

加强出错处理

注意RUNSTATS

建表要显式指定分区键(PARTITIONING KEY)

建表要创建唯一约束(主键)

适当增加索引

提高SQL技巧

SQL存储过程出错处理

当存储过程出错时，你希望在日志里看到

SQLCODE = -119

还是

SQL0119N An expression starting with "CALL_AREA_CD" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.

       呢？

      

其实DB2提供的GET DIAGNOSTICS语句可以获得文字消息：

DECLARE vMsgText VARCHAR (256);

-- 在存储过程的Exit Handler里面获取SQL错误的文本消息

GET DIAGNOSTICS EXCEPTION 1 vMsgText = MESSAGE_TEXT;

              -- 参考DB2文档《SQL Reference Volume 2》

       有了MESSAGE_TEXT，存储过程的调试和查错就方便多了。

声明临时表需要注意

指定合理的分区键

  • 建议声明临时表时显式地指定分区键。

很多时候，DB2缺省选择的分区键是非常糟糕的。

例子：PRO_TM_USR_CONSUME_MO

CVS:/DSS代码/21软件代码/05基线代码/ETL/存储过程/

版本V01.400.000，CVS版本1.8

代码第75行

测试结果：对该SESSION临时表指定与目标表TM_USR_CONSUME_MO一致的分区键后（只作此改动，其它代码不修改），原来跑6个小时的存储过程只需要不到2个小时。

 

ETL.PRO_TM_CALLVAL_USR_MO_JF

  • 尽量使关联表之间的分区键一致，并且关联条件包含所有的分区列。

目的是减少昂贵的分区间数据通信。

  • 尽量使源表、目标表的分区键一致。

目的是减少昂贵的分区间数据通信。

 

[2004-10-20]

看PRO_TM_BUSI_USE_MO_JF.SQL：

缺陷报告DSS20040985“月汇总存储过程性能优化”，其中PRO_TM_BUSI_USE_MO_JF需要把汇总拆分为两步（增加临时表）。由于修改人没意识到分区键的问题，声明临时表时没指定分区键。

问题是严重的，在没有显式指定分区键时，DB2缺省使用该表的首个字段TM_INTRVL_CD。但TM_INTRVL_CD在表中只有单一取值，所有数据在单一分区上。由此造成的性能损失是巨大的，存储过程可能比“优化”前慢很多。

定义必要的约束

创建约束的好处？

顾名思义，约束的作用就是对数据进行约束，DB2不允许把违反约束规则的数据插入到数据库。约束可以帮助我们尽早发现SQL中的逻辑错误。大部分程序缺陷是通过运行时的错误发现的，如果不定义任何约束，等于放弃了很多检测错误的机会。例：唯一约束可以避免重复运行同一个INSERT语句带来的恶果。

唯一约束、主键约束都是通过索引实施的，实际上它们无异于一个唯一索引。

最常用的约束有

  1. 1.         非空（NOT NULL）
  2. 2.         唯一（UNIQUE）

ALTER TABLE <tab name> ADD UNIQUE (columns list);

  1. 3.         主键（PRIMARY KEY）

创建必要的索引

当需要从大量数据中选出少量数据时，我们需要索引。

提高SQL性能

RUNSTATS

执行RUNSTATS命令时要加上“WITH DISTRIBUTION”选项，统计各字段的取值分布情况，这也对Access Plan的选择有影响。

CREATE INDEX KF2.IDX_CNSM_GZ ON KF2.TW_USR_CNSM_RNK_GZ (CURRMO_AMT_FEE);

SELECT * FROM KF2.TW_USR_CNSM_RNK_GZ WHERE CURRMO_AMT_FEE = 100;

SELECT * FROM KF2.TW_USR_CNSM_RNK_GZ WHERE CURRMO_AMT_FEE <> 100;

 

建议RUNSTATS选项：

RUNSTATS ON <tab name> WITH DISTRIBUTION AND INDEXES ALL

子查询尽量写成JOIN语法

看看以下三种写法

写法1：SELECT ... FROM A

WHERE A.key NOT IN (SELECT key FROM B);

 

写法2：SELECT ... FROM A

            LEFT JOIN B ON A.key = B.key

WHERE B.key is null;

写法3：SELECT ... FROM A

WHERE NOT EXISTS

(SELECT 'x' FROM B WHERE A.key = B.key);

 

避免在WHERE子句中使用表达式

       例子：

        写法1：WHERE CMCC_BRANCH_CD LIKE ‘GZ%’

        写法2：WHERE LEFT (CMCC_BRANCH_CD, 2) = ‘GZ’

       比较：

建议使用第一种写法，因为它允许使用索引。而第二种写法只能用表扫描。

 

      

       现在很多存储过程中的代码都采用了写法2。

避免group by多个表的字段

PS. 好象很多时候无法避免，只能“尽量”了。

Group by子句中尽量不要带表达式

       实例：PRO_TM_BUSI_USE_MO_JF ，v01.02.000，CVS 版本1.1

CVS:/DSS代码/21软件代码/05基线代码/ETL/存储过程/PRO_TM_BUSI_USE_MO_JF

       存储过程需要运行1个小时左右（服务器忙时）。

                                   半个小时左右（服务器空闲时）

       改为GROUP BY不带表达式（需声明临时表，并拆分为两步），原来需半个小时的存储过程只需10分钟（服务器空闲时，估计服务器有负载时需要十几分钟）。

修改原则：

假设源数据行数为N0，带表达式GROUP BY后的行数为N1。去掉GROUP BY字段中的VALUE，CASE等运算之后，GROUP BY结果为Ntmp，通常有：

N0＞Ntmp＞N1

如果N0 >> Ntmp，且Ntmp不大，则可以把语句拆分成两步，提高性能：第一步把group by中的运算去掉，然后再把第一步的结果group by一次。

 

注意：用此方法拆分SQL，目的是为了减少运算量以提高效率，如果N1和Ntmp相差不大，则不宜拆分（拆分后反而更慢）。

例子：用户月通话信息表汇总，广州数据：

N0                    9亿多

Ntmp                7亿多

N1                     接近4亿

这种情况下，拆分成两步后用时比原来更长。

UNION and UNION ALL

大家对UNION和UNION ALL了解可能比较清楚，这里就不多说了。

减少GROUP BY的字段数

  • 去掉GROUP BY子句中的常量
  • 如果SELECT的某字段在源表中只有单一取值，可以用常量代替（有时此做法可能在一定程度上影响程序的可读性）。

例子：

SELECT TM_INTRVL_CD, -1, -1, CASE(...), ...

FROM EDS.TW_USRCALL_MO_GZ200409

WHERE ...

GROUP BY TM_INTRVL_CD, -1, -1, CASE(...), ...

 

因为月通话信息表己按月份分表，EDS.TW_USRCALL_MO_GZ200409表中的TM_INTRVL_CD取值都是200409，所以可以把之写成常数,同时去掉GROUP BY子句中的常量：

SELECT 200409, -1, -1, CASE(...), ...

FROM EDS.TW_USRCALL_MO_GZ200409

WHERE ...

GROUP BY CASE(...), ...

存储过程：保持 DB2 优化器处于被通知状态

当创建了一个过程时，其单独的 SQL 查询被编译成包中的节。其中，DB2 优化器根据表的统计信息（例如，表大小或某列中数据值出现的相对频率）以及编译查询时可用的索引来选择查询的执行方案。当表经过了重大更改时，让 DB2 再次收集有关这些表的统计信息可能是个好主意。当更新了统计信息时，或者当创建了新的索引时，重新绑定那些与使用表的 SQL 过程相关联的包，以使 DB2 创建使用最新统计信息和索引的方案，这可能也是一个好主意。

 

可以使用 RUNSTATS 命令更新表的统计信息。要重新绑定与 SQL 过程关联的包，可以使用 REBIND_ROUTINE_PACKAGE 内置过程（在 DB2 V8 中可用）。例如，可以使用下面这条命令来重新绑定过程 MYSCHEMA.MYPROC 的包：

 

 

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'MYSCHEMA.MYPROC', 'ANY')

 

 

 

其中 'P' 表明该包对应于一个过程，而 'ANY' 表明 SQL 路径中的任何函数和类型都被当作函数和类型解析。（请参阅 REBIND 命令的 Command Reference 条目，以获取更多详细信息。）

原文连接：http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/0306arocena/0306arocena.html