mysql 存储过程 优化_优化这样的mysql存储过程

程序逻辑：

当解析程序从tmpXXX.txt文件中取得一条节目起始时间信息时，将其解析并插入到表current_info_table中，当取得的是一条节目结束信息时，则将其插入history_info_table中，同时根据box_id，将current_info_table中的对应信息删除。

1、接收到一条节目结束信息后，删除current_info_table中的对应信息时，有可能在current_info_table中找不到此对应信息，此时程序应略过此错误，并继续执行；

2、接收到一条节目开始信息后，应先检索current_info_table中是否有相同box_id的数据，如果有，应将此数据根据本次接收的信息进行update，而不是直接执行insert操作；

存储过程代码：

BEGIN

DECLARE cnt INT;

SELECT COUNT(*) FROM currentinfo WHERE EXISTS(SELECT * FROM loaddatainfo WHERE loaddatainfo.stbid=currentinfo.stbid) INTO @cnt;

IF @cnt>0 THEN

DELETE FROM currentinfo WHERE currentinfo.stbid IN (SELECT loaddatainfo.stbid FROM loaddatainfo WHERE loaddatainfo.flag='0');

COMMIT;

INSERT INTO currentinfo(age,sex,stbid,channel,program,begintime,endtime)

SELECT age,sex,stbid,channel,program,begintime,endtime FROM loaddatainfo

WHERE NOT EXISTS(SELECT * FROM currentinfo WHERE loaddatainfo.stbid=currentinfo.stbid) AND loaddatainfo.flag='0';

COMMIT;

ELSE

INSERT INTO currentinfo(age,sex,stbid,channel,program,begintime,endtime)

SELECT age,sex,stbid,channel,program,begintime,endtime FROM loaddatainfo

WHERE NOT EXISTS(SELECT * FROM currentinfo WHERE loaddatainfo.stbid=currentinfo.stbid) AND loaddatainfo.flag='0';

COMMIT;

END IF;

INSERT INTO historyinfo(age,sex,stbid,channel,program,begintime,endtime)

SELECT age,sex,stbid,channel,program,begintime,endtime FROM loaddatainfo

WHERE loaddatainfo.flag='1';

COMMIT;

DELETE FROM currentinfo WHERE currentinfo.stbid IN (SELECT historyinfo.stbid FROM historyinfo);

COMMIT;

INSERT INTO currentinfo(age,sex,stbid,channel,program,begintime,endtime)

SELECT age,sex,stbid,channel,program,begintime,endtime FROM loaddatainfo

WHERE loaddatainfo.id IN (SELECT MAX(loaddatainfo.id) FROM loaddatainfo GROUP BY stbid) AND loaddatainfo.flag='1';

COMMIT;

TRUNCATE loaddatainfo;

COMMIT;

END;

这样的存储过程优化起来头大呀。