【性能测试】：关于新造铺地数据，开发存储过程的一些

begin
declare counter int;
declare i int;
declare myuserid int;
declare myorderid int;
set counter=item;
set myuserid = userid;
set myorderid = orderid;
while counter >=1 do
INSERT INTO `ham`.`b_m_m` (`id`,`type_desc`) 
VALUES (myuserid, '金卡');
set counter =counter-1;
set myuserid = myuserid+1;
set myorderid = myorderid+1;
set i=i+1;
if i=1000 then
set i=0;
end if;
end while;
end

 

做性能测试，会要对测试环境的数据进行造数，保证数据库表的数据量在一定的级别，这样才能测试出数据库的真实性能

如果要对一张表生成千万级别的数据量，通过insert插入的方式就太慢了，就要使用到存储过程

一，创建序列

CREATE SEQUENCE DB.WX_REDPOCKET_ADJUSTLIMITINF_NO
  AS BIGINT
  START WITH 1
  CACHE 20
  MAXVALUE  5000000
  ORDER

二，写存储过程

CREATE PROCEDURE EA_ACCOUNT_INF_PRO (IN IN_NUM integer) -- 要插入的数据量
BEGIN
DECLARE COUNT_1 integer;
DECLARE LCD_PRDNO_FLOW VARCHAR(15);--流水号
SET COUNT_1=0;
WHILE COUNT_1 < IN_NUM
DO
    SELECT cast(nextval for WX_REDPOCKET_ADJUSTLIMITINF_NO as char(15)) INTO LCD_PRDNO_FLOW FROM sysibm.sysdummy1;
    insert into EA_ACCOUNT_INF (EAI_ACCOUNTID, EAI_IDENTIFYCUSTID, EAI_CHANNELID, EAI_BUSSITYPE, EAI_CURRTYPE, EAI_BALANCEDIR, EAI_BALANCE, EAI_USABLEBALANCE, EAI_WITHDRAWLIMIT, EAI_LINECREDIT, EAI_FROZENAMONT, EAI_ACCUMULATE, EAI_ACCOUNTSTATE, EAI_ACCOUNTATTRIBUTE, EAI_BRNO, EAI_ACCOUNTNAME, EAI_OPENTIME, EAI_CHANGETIME, EAI_CLOSEDTIME, EAI_CLOSEDREASON, EAI_PASSWORD, EAI_OPENBRNO, EAI_SUBJECTID, EAI_CARDNO, EAI_CARDNAME, EAI_MOBILENO, EAI_CUSTID, EAI_ISCHECKPHONENO, EAI_ISCHECKDK, EAI_FAILNUMS, EAI_LASTFAILTIME, EAI_ACCNO) values (LCD_PRDNO_FLOW, '211000000826', '101', '100', '156', '00', 283.73, 283.73, 0.00, 0.00, 0.00, null, '00', 'Y', null, null, null, null, null, null, 'FACA2E872169030C', null, null, '6222023602057198962', '李聂渝', '11111111111', '111000000001', '00', '00', 0, null, null);
    SET COUNT_1=COUNT_1+1;
END WHILE;
END

三，调用存储过程

call EA_ACCOUNT_INF_PRO(200000)

插入200000条数据