前提是:不能直接使用select ...into这种SQL语句来实现,只能通过存储过程游标游标是select一个数据库的某个表的所有字段。在使用游标时,进行相关算术运算,值设置给我目标表的字段。然后insert数据到目标表。由于算术运算需要读取一个配置文件,所以不能写死,我是在JAVA程序中生成存储过程脚本的。
现在的问题是,表中只有102条记录,可存储过程执行还要5秒左右。觉得性能有问题。以后数据可能上万,速度是个问题。只能给个示例脚本了。没有可以完全测试的。主键:start_time,stop_time,moi
DELIMITER //
DROP PROCEDURE IF EXISTS nbi_north_db.sp_07_01//
CREATE PROCEDURE nbi_north_db.sp_07_01(INOUT ret INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE `UtranCell_start_time` DATETIME;
DECLARE `UtranCell_stop_time` DATETIME;
DECLARE `UtranCell_moi` VARCHAR(128);
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.5` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.4` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.3` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.2` FLOAT DEFAULT 0;DECLARE cursor_UtranCell_R1_07_01 CURSOR FOR SELECT
`start_time`,
`stop_time`,
`moi`,
`UC1126_5_Avg`,
`UC969_0`,
`UC969_7`,
`UC969_8`
FROM nbi_south_db.`UtranCell_R1_07_01`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;OPEN cursor_UtranCell_R1_07_01;
loop_UtranCell_R1_07_01:LOOP
FETCH cursor_UtranCell_R1_07_01 INTO
`UtranCell_start_time`,
`UtranCell_stop_time`,
`UtranCell_moi`,
`UtranCell_R1_07_01.UC1126_5_Avg`,
`UtranCell_R1_07_01.UC969_0`,
`UtranCell_R1_07_01.UC969_7`,
`UtranCell_R1_07_01.UC969_8`;
IF done THEN
SET done=0;
LEAVE loop_UtranCell_R1_07_01;
END IF;SET `UtranCell_SHO.NbrUEPerRLNbrL.5`=`UtranCell_R1_07_01.UC25_9_Avg` + `UtranCell_R1_07_01.UC25_10_Avg` + `UtranCell_R1_07_01.UC25_11_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.4`=`UtranCell_R1_07_01.UC25_6_Avg` + `UtranCell_R1_07_01.UC25_7_Avg` + `UtranCell_R1_07_01.UC25_8_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.3`=`UtranCell_R1_07_01.UC25_3_Avg` + `UtranCell_R1_07_01.UC25_4_Avg` + `UtranCell_R1_07_01.UC25_5_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.2`=`UtranCell_R1_07_01.UC25_1_Avg` + `UtranCell_R1_07_01.UC25_2_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.1`=`UtranCell_R1_07_01.UC25_0_Avg`;INSERT INTO nbi_north_db.UtranCell_15(`start_time`,
`stop_time`,
`moi`,
`SHO.NbrUEPerRLNbrL.5`,
`SHO.NbrUEPerRLNbrL.4`,
`SHO.NbrUEPerRLNbrL.3`,
`SHO.NbrUEPerRLNbrL.2`
VALUES(
`UtranCell_start_time`,
`UtranCell_stop_time`,
`UtranCell_moi`,
`UtranCell_SHO.NbrUEPerRLNbrL.5`,
`UtranCell_SHO.NbrUEPerRLNbrL.4`,
`UtranCell_SHO.NbrUEPerRLNbrL.3`,
`UtranCell_SHO.NbrUEPerRLNbrL.2`
ON DUPLICATE KEY UPDATE
`SHO.NbrUEPerRLNbrL.5`=`UtranCell_SHO.NbrUEPerRLNbrL.5`,
`SHO.NbrUEPerRLNbrL.4`=`UtranCell_SHO.NbrUEPerRLNbrL.4`,
`SHO.NbrUEPerRLNbrL.3`=`UtranCell_SHO.NbrUEPerRLNbrL.3`,
`SHO.NbrUEPerRLNbrL.2`=`UtranCell_SHO.NbrUEPerRLNbrL.2`;END LOOP loop_UtranCell_R1_07_01;
CLOSE cursor_UtranCell_R1_07_01;
SET ret=0;
END//
DELIMITER ;
也就是说,遍历nbi_south_db.`UtranCell_R1_07_01`表的数据,拿它的字段作相关算术运算,得到的值设置给目标表的字段。目标表是nbi_north_db.`UtranCell_15`
现在的问题是,表中只有102条记录,可存储过程执行还要5秒左右。觉得性能有问题。以后数据可能上万,速度是个问题。只能给个示例脚本了。没有可以完全测试的。主键:start_time,stop_time,moi
DELIMITER //
DROP PROCEDURE IF EXISTS nbi_north_db.sp_07_01//
CREATE PROCEDURE nbi_north_db.sp_07_01(INOUT ret INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE `UtranCell_start_time` DATETIME;
DECLARE `UtranCell_stop_time` DATETIME;
DECLARE `UtranCell_moi` VARCHAR(128);
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.5` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.4` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.3` FLOAT DEFAULT 0;
DECLARE `UtranCell_SHO.NbrUEPerRLNbrL.2` FLOAT DEFAULT 0;DECLARE cursor_UtranCell_R1_07_01 CURSOR FOR SELECT
`start_time`,
`stop_time`,
`moi`,
`UC1126_5_Avg`,
`UC969_0`,
`UC969_7`,
`UC969_8`
FROM nbi_south_db.`UtranCell_R1_07_01`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;OPEN cursor_UtranCell_R1_07_01;
loop_UtranCell_R1_07_01:LOOP
FETCH cursor_UtranCell_R1_07_01 INTO
`UtranCell_start_time`,
`UtranCell_stop_time`,
`UtranCell_moi`,
`UtranCell_R1_07_01.UC1126_5_Avg`,
`UtranCell_R1_07_01.UC969_0`,
`UtranCell_R1_07_01.UC969_7`,
`UtranCell_R1_07_01.UC969_8`;
IF done THEN
SET done=0;
LEAVE loop_UtranCell_R1_07_01;
END IF;SET `UtranCell_SHO.NbrUEPerRLNbrL.5`=`UtranCell_R1_07_01.UC25_9_Avg` + `UtranCell_R1_07_01.UC25_10_Avg` + `UtranCell_R1_07_01.UC25_11_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.4`=`UtranCell_R1_07_01.UC25_6_Avg` + `UtranCell_R1_07_01.UC25_7_Avg` + `UtranCell_R1_07_01.UC25_8_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.3`=`UtranCell_R1_07_01.UC25_3_Avg` + `UtranCell_R1_07_01.UC25_4_Avg` + `UtranCell_R1_07_01.UC25_5_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.2`=`UtranCell_R1_07_01.UC25_1_Avg` + `UtranCell_R1_07_01.UC25_2_Avg`;
SET `UtranCell_SHO.NbrUEPerRLNbrL.1`=`UtranCell_R1_07_01.UC25_0_Avg`;INSERT INTO nbi_north_db.UtranCell_15(`start_time`,
`stop_time`,
`moi`,
`SHO.NbrUEPerRLNbrL.5`,
`SHO.NbrUEPerRLNbrL.4`,
`SHO.NbrUEPerRLNbrL.3`,
`SHO.NbrUEPerRLNbrL.2`
VALUES(
`UtranCell_start_time`,
`UtranCell_stop_time`,
`UtranCell_moi`,
`UtranCell_SHO.NbrUEPerRLNbrL.5`,
`UtranCell_SHO.NbrUEPerRLNbrL.4`,
`UtranCell_SHO.NbrUEPerRLNbrL.3`,
`UtranCell_SHO.NbrUEPerRLNbrL.2`
ON DUPLICATE KEY UPDATE
`SHO.NbrUEPerRLNbrL.5`=`UtranCell_SHO.NbrUEPerRLNbrL.5`,
`SHO.NbrUEPerRLNbrL.4`=`UtranCell_SHO.NbrUEPerRLNbrL.4`,
`SHO.NbrUEPerRLNbrL.3`=`UtranCell_SHO.NbrUEPerRLNbrL.3`,
`SHO.NbrUEPerRLNbrL.2`=`UtranCell_SHO.NbrUEPerRLNbrL.2`;END LOOP loop_UtranCell_R1_07_01;
CLOSE cursor_UtranCell_R1_07_01;
SET ret=0;
END//
DELIMITER ;
也就是说,遍历nbi_south_db.`UtranCell_R1_07_01`表的数据,拿它的字段作相关算术运算,得到的值设置给目标表的字段。目标表是nbi_north_db.`UtranCell_15`
解决方案 »
- 查询语句 求高手赐教
- 请问这样如何写SQL语句?
- 可以使用drop user命令删除user表中的user字段为空的那条记录吗?
- mysql集群怎么确定数据节点的数量呢?
- mysql select *... where id in (select 字符串 from ... )问题?
- 执行了一个比较简单Mysql子查询,但是速度很慢,请各位高手帮忙看看问题所在和提供解决方法.
- 如何读取blob字段的值
- mysql的匹配查询问题@@@@@@@@@@@@@@@@@@@@@@@(送100分,只要问题解决)
- MySql 数据量大时索引无效!
- “System.InvalidOperationException”类型的未经处理的异常出现在 MySql.Data.dll 中。
- 关于MySql注册表Key值?
- 【向ACMAIN_CHM提问】关于备份账户
用游标,速度不会快,最好在语言中取得配置文件的中运算, 字符串累加生成SQL语句,动态执行
`UtranCell_start_time`,
`UtranCell_stop_time`,
`UtranCell_moi`,
`UtranCell_SHO.NbrUEPerRLNbrL.5`,
`UtranCell_SHO.NbrUEPerRLNbrL.4`,
`UtranCell_SHO.NbrUEPerRLNbrL.3`,
`UtranCell_SHO.NbrUEPerRLNbrL.2`纠正一下,这段后面少了个)
我就是用字符串累加生成的存储过程脚本。问题是,我这个程序是offline的,在客户现场,我的这程序是不在运行范围的。现场是别人的程序调用我的存储过程。很多因素造成我这里不能直接拼SQL语句,而是使用存储过程。首先,领导是让我用存储过程。
刚开始我没多想,是这么写的,领导一看就提出问题了。
因项目差不多成型了,写代码来拼这个SP也历时好久。如果使用insert into ... select,在select后面 取出一个表字段作算术运算时,需要加上where moi等于另一表的moi字段值,只有通过连接那另一表啊。这样性能也好不到哪里去吧?
这样更慢吧表达式类似这样:
HSDPA.MeanRlcThroughputDl = UC1556_5 / UC1566_5 * 8000
CR.CodeResUsed.SF4 = (4 - UC1126_0_Avg) / 7
你最好在存储过程中,加几条 insert into traceTime values (UNIX_TIMESTAMP())这样分析一下速度慢在哪个环节。
上面代码中,`rnc_moi`是游标循环中的一个变量的值。今天运行SP,时间更长了,7秒多钟了!
如果格式是固定的,类似TXT文件,直接用LOAD DATA INFILE导入表中
还有,配置文件根本无法使用load data infile
里面的表达式有的长有的短,有的是直接算术运算,有的是聚合函数然后进行算术运算。我打算试试在JAVA程序中,拼接SQL语句,当然,语句会相当的长。在SP中直接写SQL语句。不使用游标
目前性能太差了
这帖暂不结