求mysql高手指点 存储过程效率问题

[hanjin8307]

下次麻烦注意高亮！


在java中调用存储过程 往mysql的表里导入数据,每次600条 循环导入 但是速度非常慢,同样的程序在ms SQL server中速度很快 本地导入1W条数据大概3秒左右..现在mysql要用4分钟左右.
过程的sql语句如下

CREATE PROCEDURE `P_import_messege_normal`(t_code varchar (8000), 

 t_UserID varchar (50), 

 t_content varchar (4000), 

 t_msgidentity varchar (50),

 t_sendType varchar(1), 

 t_sendTime varchar(50), 

 t_Enterprise_id varchar (50), 

 t_smsCount int,

 t_isadmin varchar (1),

 t_isanwsor varchar (1), 

 t_backcontens varchar (150),
out t_reaminpoint int,
t_times int)
BEGIN



DECLARE   t_time  datetime  ;

DECLARE   t_COUNT int ;

DECLARE   t_AGENTID varchar(50);

DECLARE   t_STAT int ;

DECLARE   t_SPCODE  varchar(20);

DECLARE   t_MCODE VARCHAR(8000);

DECLARE   t_i int;

DECLARE   t_i2  int;

DECLARE   str  varchar(20);

DECLARE   t_msgidentity2  int;

DECLARE   t_POINT INT ;





START TRANSACTION;



select   now() into  t_time ;



SELECT  agentid into  t_AGENTID  FROM  User_info where userid=t_Enterprise_id;



 select max(MsgIdentify)  into t_msgidentity2 from User_send_info  where   Enterprise_id=t_Enterprise_id;



if t_times=0 then 

  if t_msgidentity<t_msgidentity2 then

      set   t_msgidentity=t_msgidentity2+1;

    end if; 

end if;



if(t_msgidentity is null ) then



     set t_msgidentity=1;

 end if;







if  t_msgidentity=9999   then



   set t_msgidentity=1000;

delete from User_send_info where Enterprise_id=t_Enterprise_id and  MsgIdentify>0 and MsgIdentify<9000;

delete from SMS_Send_New_Number where Enterprise_id=t_Enterprise_id and  MsgIdentify>0 and MsgIdentify<9000;



update User_send_info set MsgIdentify= MsgIdentify-9000 and   Enterprise_id=t_Enterprise_id ;

update  SMS_Send_New_Number  set MsgIdentify=MsgIdentify-9000 and  Enterprise_id=t_Enterprise_id ;



end if;



set  t_msgidentity=CONCAT('0000',t_msgidentity);

set t_msgidentity=right(t_msgidentity,4);



 drop table  if exists  t_codetable;

 drop table  if exists  t_codetable1;

create TEMPORARY  table t_codetable(

   mcode text

);

create TEMPORARY  table t_codetable1(

   mcode text

);



    set t_MCODE=ltrim(t_code);

    set t_MCODE=rtrim(t_MCODE);

    set t_i=POSITION(';' IN t_MCODE);







    WHILE    t_i >=1    do

         set  str=left(t_MCODE,t_i-1);

         insert t_codetable1(mcode) values(str);

        set t_MCODE=substring(t_MCODE,t_i+1,length(t_MCODE)-t_i);

       set t_i=POSITION(';' in t_MCODE);

    end WHILE ;



insert t_codetable1(mcode) values(t_MCODE);

insert   t_codetable(mcode)  select distinct   mcode from  t_codetable1 ;



select * from t_codetable;

delete from  t_codetable where mcode='' or mcode is null  or mcode=';'  ;



select  spshortcode into  t_SPCODE from user_info where  userid=t_Enterprise_id;

set  t_SPCODE=CONCAT('0000',t_SPCODE);

set t_SPCODE=right(t_SPCODE,4);



SELECT count(mcode) into  t_COUNT  FROM  t_codetable  ;

IF t_COUNT>20 then



   SET t_STAT=1;



ELSE

   SET t_STAT=2;

 end if;

IF t_sendTime='no' then



  set t_sendTime='' ;

end if;







IF t_sendType=1 then

    SET t_POINT=10;



ELSE

   SET t_POINT=9;

end if;



iF t_isadmin=1 then

    select  SMSRemainedCount into t_smsCount from User_info  where  UserID=t_Enterprise_id;



ELSE

  select  SMSRemainedCount into t_smsCount  from Client_UserInfo  where  UserID=t_UserID AND

   Enterprise_id=t_Enterprise_id;



 end if;

set  t_reaminpoint =t_smsCount;/*当前余额*/

/*p判断余额是否足够*/

if t_smsCount>(t_COUNT*t_POINT)   then



iF t_isadmin=1 then



UPDATe  User_info SET SMSRemainedCount=SMSRemainedCount-(t_COUNT*t_POINT) WHERE UserID=t_Enterprise_id;



ELSE

  UPDATE    Client_UserInfo SET  SMSRemainedCount=SMSRemainedCount-(t_COUNT*t_POINT) WHERE UserID=t_UserID AND

   Enterprise_id=t_Enterprise_id;



 end if;



set t_reaminpoint=t_reaminpoint-(t_COUNT*t_POINT);/*扣点后的值*/



INSERT    SMS_Send_New_Number(userid,MsgIdentify,smsSendNumber,numberType,Enterprise_id)

select  distinct t_UserID,CONCAT(t_SPCODE,t_msgidentity) , mcode,1,t_Enterprise_id from t_codetable where  left(mcode,3) >133;





INSERT    SMS_Send_New_Number(userid,MsgIdentify,smsSendNumber,numberType, Enterprise_id)


 select  distinct t_UserID,CONCAT(t_SPCODE,t_msgidentity) , mcode,2,t_Enterprise_id from  t_codetable where  left(mcode,3) <=133

and  left(mcode,2) =13;







INSERT    SMS_Send_New_Number(userid,MsgIdentify,smsSendNumber,numberType, Enterprise_id)

select  distinct t_UserID,CONCAT(t_SPCODE,t_msgidentity) ,mcode,3,t_Enterprise_id from  t_codetable where left(mcode,2 )<>13

and  left(mcode,2) <>15 ;









if not exists(select CONCAT(t_SPCODE,t_msgidentity)  from User_send_info where id=CONCAT(t_SPCODE,t_msgidentity)  )then



INSERT INTO  User_send_info

(id,USERID,userType,agentid,CheckStatus,DataCount,MsgIdentify,spcode,

SMSContent,Operatedate,sendType,SendTime,Enterprise_id,isanwsor, backcontens,src_id)

values (CONCAT(t_SPCODE,t_msgidentity),t_UserID,0,t_AGENTID,t_STAT,t_COUNT,t_msgidentity,t_SPCODE,t_content,t_time,t_sendType

,t_sendTime,t_Enterprise_id,t_isanwsor,t_backcontens,CONCAT(t_SPCODE,t_msgidentity) );





else



    update User_send_info set DataCount=DataCount+t_COUNT where id=CONCAT(t_SPCODE,t_msgidentity) ;

   end if;

end if;

commit ;



end;

[cmz0706]

能不能加一点注释啊。看不懂啊啊

[hanjin8307]

t_* 都是变量和参数,t_code是多个号码拼的字符串 以;分割 前面大段是将t_code里的每个号码拆分出来放到临时表里 后面就是将这些号码 一个号码一条记录插到表里 其他的一些处理大家可以无视了