DB2 sql优化

 

2. DB2分页优化技术

　　在WEB查询中，我们一般将大量数据分切成页，一页一页地推送给BROWER端。另外，基本表信息中又大量使用字典表，如果将这些数据与字典表关联，将会极大加重服务器的重担。

　　如有一个记录均超过100W以上记录的A表，里面有一个A1,A2,A3,A4这四个字段，表示某个字典的代码，为了关联出与它们对应的代码意义(设它们的字典分别为D_A1,D_A2,D_A3,D_A4)，你可以这样写相关的SELECT- SQL,

      Select a.*,a1.a1_NAME,a2.a2_NAME,a3.a3_NAME,a4.a4_NAME
      From A
      Left join  D_a1  a1 on  A.a1=A1.A1
      Left join  D_a2  a2 on  A.a2=A2.A2
      Left join  D_a3  a3 on  A.a3=A3.A3
      Left join  D_a4  a4 on  A.a4=A4.A3

　　此 SQL当然没有什么错误，而且也能关联出结果。但问题是：在100W以上的记录上去关联字典表（如果字典表也比较大的话），这会是一个很耗时的查询。下面给出一个优化 SQL(每页25条记录，显示满足条件的第二页)：

      select t.*, 
             a.字典名1,
             a.字典名2 
      from (
            SELECT t.* 
            FROM (select a.*,
                      rownumber() over() AS MyTempRecordeNo 
                  from [你要查询的表] a 
	          Where [你的条件]
	          ) t 
            WHERE t.MyTempRecordeNo BETWEEN 26 AND 50
            ) t 
      left join [字典表1]  a 
            ON 相关条件1 
      left join [字典表2] b 
            ON 相关条件2
      with ur

使用存储过程的通用分页方式：

db2 通用分页存储过程

CREATE   PROCEDURE  DB2ADMIN.PROC_GETPAGE
 (INOUT CURRENTPAGE  INTEGER , 
   IN  STRSQL  VARCHAR ( 500 ), 
   IN  PAGESIZE  INTEGER , 
  OUT TOTALPAGE  INTEGER , 
  OUT TOTALRECORD  INTEGER
 ) 
  SPECIFIC DB2ADMIN.SQL080201103307110
  DYNAMIC RESULT SETS  1
  LANGUAGE SQL
   NOT  DETERMINISTIC
  CALLED  ON   NULL  INPUT
  READS SQL DATA
  INHERIT SPECIAL REGISTERS
   BEGIN
     DECLARE  tmpsql    VARCHAR ( 1000 );    --  主语句
     DECLARE  orderFiled  VARCHAR ( 50 );
     DECLARE  s   VARCHAR ( 1000 );
     DECLARE  v_Start   INTEGER ;
     DECLARE  v_End  INTEGER ;
     DECLARE  result  CURSOR   WITH   RETURN   TO  CALLER  FOR  S2;
    
     SET  orderFiled  = getOrderField(strsql);
   
     if (length(orderFiled ) > 0 )  then
          set  orderFiled  = '  order by  '   ||  orderFiled ;
     end   if ;
    


     SET  s   = getSQL(strsql);
     set  tmpsql  =   ' select count(*) from ( '   ||  strsql  ||   ' ) as a ' ;
     prepare  s2  from  tmpsql;
     open  result;
         fetch  result  into  totalrecord; --  总记录数
     close  result; 
    
     if (pagesize  =   0 )  then
         set  pagesize  =   20 ; --  每页显示数
     end   if ; 

     set  totalPage  =  (totalrecord  -   1 )  /  pagesize  +   1 ; --  总页数 



     if (currentPage  <   1 )  then
         set  currentPage  =   1 ; --  当前页
     else
         if (currentPage  >  totalPage)  then
             set  currentPage  =  totalPage;
         end   if ;
     end   if ; 

     set  v_Start  =  (currentPage - 1 )  *  pagesize ;
     set  v_End  =  currentPage  *  pagesize;


     set   tmpsql  = ' select   *   from    '   ||
            ' (select   rownumber()   over()   as   row,  '   ||
     ' w.*   from   ( select   *   from   (   '   ||  s  ||      ' ) n   '   ||  orderFiled   ||   ' ) w)   w1   where   row between  '    ||   char (v_Start )  ||    '  and   '    ||   char (v_End );
    
     prepare  s2  from  tmpsql ;
     open  result;
    
END ;

使用了两个java函数

import java.util.regex.*;

import COM.ibm.db2.app.UDF;

public class FunctionDB2 extends UDF
{
 public static String getOrderField(String sql)
 {
  Pattern Regex = Pattern.compile("select(.+)order\\s*by\\s*(.+)",
    Pattern.CANON_EQ | Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE);
   Matcher RegexMatcher = Regex.matcher(sql);
   if (RegexMatcher.find()) {
    return RegexMatcher.group(2);
   }
   return "";
 }
 
 public static String getSQL(String sql)
 {
  Pattern Regex = Pattern.compile("(.+)order\\s*by\\s*(.+)",
    Pattern.CANON_EQ | Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE);
   Matcher RegexMatcher = Regex.matcher(sql);
   if (RegexMatcher.find()) {
    return RegexMatcher.group(1);
   }
   return sql;
 }
 
 
 
}

另外的通用分页存储过程：

 

CREATE PROCEDURE SALES.DB2PAGINATION(IN ITBNAME VARCHAR(2000),-- 表名

IN ISHOWFIELD VARCHAR(1000),-- 显示字段

IN IJOIN VARCHAR(1000),-- 联接条件（如：内联、外联）

IN IWHERE VARCHAR(2000),-- 查询条件 (注意: 不要加 WHERE)

IN IORDER VARCHAR(100),-- 排序条件 (注意: 不要加 ORDER BY)

IN IPAGESIZE INTEGER,-- 页尺寸 如果为0 默认返回前一百万条数据可以认为是返回所有数据

INOUT IOCURRENTPAGEIX INTEGER,-- 输入和输出：当前页

OUT OPAGESTARTROW INTEGER,-- 输出：当前开始行

OUT OPAGEENDROW INTEGER,-- 输出：当前结束行

OUT OTOTALROWS INTEGER,-- 输出：当前总记录数

OUT OHASPREVIOUSPAGE INTEGER,-- 输出：是否有上一页

OUT OHASNEXTPAGE INTEGER,-- 输出：是否有下一页

OUT OTOTALPAGES INTEGER,-- 输出：总页数

OUT OERROR VARCHAR(100))-- 输出：错误信息

RESULT SETS 1

MODIFIES SQL DATA

NOT DETERMINISTIC

LANGUAGE SQL

BEGIN

/**//*----------------------------------------------------------------

* Copyright (C) 2007  Huacius

* 版权所有。

*

* 存储过程分页

*

*

//-----------------------------------------------------------------------*/

DECLARE STRSQL   VARCHAR(6000);-- 主语句

DECLARE result CURSOR WITH RETURN TO CALLER FOR S2;

DECLARE exit handler FOR sqlexception-- 异常捕获

BEGIN

set OERROR = 'error!';

END;

-- BODY start --

if(iwhere <> '') then

set iwhere = ' where ' || iwhere;

end if;

if(iorder <> '') then

set iorder = 'order by ' || iorder;

end if;

if(ijoin <> '') then

set ijoin = ' ' || ijoin;

end if;

set strsql = 'select count(*) from ' || itbname || ijoin || iwhere;

prepare s2 from strsql;

open result;

fetch result into ototalrows;-- 总记录数

close result;

if(ipagesize = 0) then

set ipagesize = 1000000;-- 每页显示数

end if;

set ototalpages = (ototalrows - 1) / ipagesize + 1;-- 总页数

if(iocurrentpageix < 1) then

set iocurrentpageix = 1;-- 当前页

else

if(iocurrentpageix > ototalpages) then

set iocurrentpageix = ototalpages;

end if;

end if;

set opagestartrow = ipagesize * (iocurrentpageix -1) + 1;-- 每页开始数

if(iocurrentpageix = ototalpages) then

set opageendrow = ototalrows;-- 每页结束数

else

set opageendrow = ipagesize * iocurrentpageix;

end if;

if(iocurrentpageix > 1) then

set ohaspreviouspage = 1;-- 是否有上一页

else

set ohaspreviouspage = 0;

end if;

if(iocurrentpageix < ototalpages) then

set ohasnextpage = 1;-- 是否有下一页

else

set ohasnextpage = 0;

end if;

set strsql = 'select * from (select rownumber() over(' || iorder || ') as rownum,'

|| ishowfield

|| ' from '

|| itbname

|| ijoin

|| iwhere

|| ') as temp where rownum between ' || rtrim(char(opagestartrow)) || ' and '

|| rtrim(char(opageendrow));

prepare s2 from strsql;

open result;

-- BODY end --

END

DB2存储过程分页测试

 

CREATE PROCEDURE TEST ( IN IN_START SMALLINT,
                        IN IN_SIZE SMALLINT )
        DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- DB2下通过传递参数控制显示记录的范围

-- SQL 存储过程
        -- IN_START 记录起点
        -- IN_SIZE  记录条数
------------------------------------------------------------------------
P1: BEGIN
        -- 声明游标
        DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT *
          FROM (SELECT ROW_NUMBER() OVER() as ROW_NO, T.* FROM TOPICIS.DM_HY AS T) AS TEMP
         WHERE TEMP.ROW_NO > IN_START
           AND TEMP.ROW_NO < IN_START + IN_SIZE;

        OPEN cursor1;
END P1

 

 

DB2分页查询
SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20

以上表示提取第10到20的纪录

select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex ) AS a1
where a1.rowid > startIndex

 

 

 

DB2 SQL查询结果集自动编号、返回条数范围控制研究

摘要：

DB2 SQL语句实现查询的记录自动编号、并根据编号进一步实现记录范围控制、分页查询。

 

环境：

IBM DB2 Universal V9.1

Windows XP Professional

 

要求：写一个SQL语句，在查询结果中要显示出记录序号，这个序号是是根据记录顺序自动生成的。

 

测试初始化DB2 SQL脚本：

 

drop table t_test;

create table t_test

 (bs        bigint          not null  not null generated by default as identity,

  username  varchar(20)     not null,

  password  varchar(20),

  remark    varchar(200)

 );

 

comment on t_test

 (bs is '标识',

  username is '用户名',

  password is '密码',

  remark is '备注'

 );

 

insert into t_test(username, password) values ('aaa', 'aaa'),

('bbb', 'bbb'),

('ccc', 'ccc'),

('ddd', 'ddd'),

('eee', 'eee'),

('fff', 'fff'),

('ggg', 'ggg');

 

实现：

 

1、实现查询记录编号

要求：查询bs不等于1的所有记录。

select t.*, ROW_NUMBER() OVER() as ROW_NO

 from t_test t

where t.bs <> 1

 

查询结果如下：

bs  username password remark    ROW_NO 

---------------------------------------

2   'bbb'   'bbb'   '[Null]'    1

3   'ccc'   'ccc'   '[Null]'    2

4   'ddd'   'ddd'   '[Null]'    3

5   'eee'   'eee'   '[Null]'    4

6   'fff'   'fff'   '[Null]'    5

7   'ggg'   'ggg'   '[Null]'    6

 

说明：ROW_NUMBER() OVER() 是用来产生编号序列，从1开始编号，为整数。

as ROW_NO 是为编号列设定显示名称。

 

2、实现按照记录范围进行查询

要求：要求查询第2-5条记录。

select *

  from (select t.*, ROW_NUMBER() OVER() as ROW_NO

          from t_test t) as w

 where ROW_NO >= 2

 and ROW_NO <=5

 

查询结果如下：

bs  username password remark    ROW_NO 

---------------------------------------

2   'bbb'   'bbb'   '[Null]'    2

3   'ccc'   'ccc'   '[Null]'    3

4   'ddd'   'ddd'   '[Null]'    4

5   'eee'   'eee'   '[Null]'    5

 

3、实现SQL分页查询

对2中的SQL进行扩展，将范围起始编号作为变量传递给SQL就可以实现分页查询了。当然这个还需要程序的支持，掌握这个原理就很容易了，在此不做介绍。

 

总结：

对记录返回控制是数据库系统的特性，不同的数据库系统有不同的实现。SQL直接对结果集记录控制效率上一般是最好的。结合高级编程语言，可以实现复杂的分页查询。

 

 

DB2 SQL TOP 解决方式：

 

我们都知道在sql server中的如下sql语句是正确的
select top 20 * from tablename order by id desc
而在DB2中，这句话就存在问题了，系统会报错。

解决方法如下：
select * from tablename order by id desc fetch first N rows only
这样就可以了

我的语句是strSQL = "select  CostBill_Master.*,GT_USERINFO.USRNAM from CostBill_Master,GT_USERINFO where  CostBill_Master.DPTIDT='"+branchId+"' and GT_USERINFO.USRIDT=CostBill_Master.USERIDT order by BillNo desc fetch first 10 rows only";

转载于:https://blog.51cto.com/zhaocz/118141