深圳全飞鸿

标题: MOVE DATA的解决方案 [打印本页]

作者: zhgc 时间: 2021-9-21 15:57
标题: MOVE DATA的解决方案

DELIMITER $
DROP PROCEDURE IF EXISTS `sfc`.`MOVE_DATA_R117`$
CREATE DEFINER=`root`@`%` PROCEDURE `MOVE_DATA_R117`(
IN P_WORDATE VARCHAR(8)
)
BEGIN
insert into sfc.r_sn_detail_h
select b.* from sfc.r_station_rec_t a,
sfc.r_sn_detail_t b
where a.idd=b.track_no
and a.work_date=P_WORDATE;
delete a from sfc.r_sn_detail_t a inner join
sfc.r_station_rec_t b on a.track_no=b.idd
where b.work_date=P_WORDATE;
END$
DELIMITER ;

复制代码

单一执行到四个并行时的速度变化（结论是整体并没有更省时间，瓶颈在哪？）

(380915 row(s) affected)
Execution Time : 00:02:57:872
Transfer Time  : 00:00:00:000
Total Time    : 00:02:57:872

(504996 row(s) affected)
Execution Time : 00:04:39:554
Transfer Time  : 00:00:00:000
Total Time    : 00:04:39:554

(532023 row(s) affected)
Execution Time : 00:05:22:953
Transfer Time  : 00:00:00:016
Total Time    : 00:05:22:969

(489892 row(s) affected)
Execution Time : 00:05:09:007
Transfer Time  : 00:00:00:000
Total Time    : 00:05:09:007

(425568 row(s) affected)
Execution Time : 00:05:04:654
Transfer Time  : 00:00:00:000
Total Time    : 00:05:04:654

(489373 row(s) affected)
Execution Time : 00:04:56:636
Transfer Time  : 00:00:00:000
Total Time    : 00:04:56:636

(518097 row(s) affected)
Execution Time : 00:05:15:684
Transfer Time  : 00:00:00:000
Total Time    : 00:05:15:684

(559180 row(s) affected)
Execution Time : 00:05:54:606
Transfer Time  : 00:00:00:000
Total Time    : 00:05:54:606

(571230 row(s) affected)
Execution Time : 00:10:26:999
Transfer Time  : 00:00:00:000
Total Time    : 00:10:26:999

(548312 row(s) affected)
Execution Time : 00:11:55:233
Transfer Time  : 00:00:00:000
Total Time    : 00:11:55:233

(535491 row(s) affected)
Execution Time : 00:10:48:574
Transfer Time  : 00:00:00:000
Total Time    : 00:10:48:574

(523078 row(s) affected)
Execution Time : 00:09:00:060
Transfer Time  : 00:00:00:000
Total Time    : 00:09:00:060

(528500 row(s) affected)
Execution Time : 00:12:13:064
Transfer Time  : 00:00:00:000
Total Time    : 00:12:13:064

(552592 row(s) affected)
Execution Time : 00:13:35:043
Transfer Time  : 00:00:00:000
Total Time    : 00:13:35:043

(572276 row(s) affected)
Execution Time : 00:14:40:049
Transfer Time  : 00:00:00:000
Total Time    : 00:14:40:049

(572179 row(s) affected)
Execution Time : 00:13:45:963
Transfer Time  : 00:00:00:000
Total Time    : 00:13:45:963

(481601 row(s) affected)
Execution Time : 00:11:02:458
Transfer Time  : 00:00:00:000
Total Time    : 00:11:02:458

(477270 row(s) affected)
Execution Time : 00:10:45:938
Transfer Time  : 00:00:00:000
Total Time    : 00:10:45:938

(461090 row(s) affected)
Execution Time : 00:12:10:677
Transfer Time  : 00:00:00:000
Total Time    : 00:12:10:677

(484126 row(s) affected)
Execution Time : 00:11:28:557
Transfer Time  : 00:00:00:000
Total Time    : 00:11:28:557

(468698 row(s) affected)
Execution Time : 00:11:37:075
Transfer Time  : 00:00:00:000
Total Time    : 00:11:37:075

(396346 row(s) affected)
Execution Time : 00:11:12:365
Transfer Time  : 00:00:00:000
Total Time    : 00:11:12:365

(443843 row(s) affected)
Execution Time : 00:17:18:592
Transfer Time  : 00:00:00:000
Total Time    : 00:17:18:592

(462243 row(s) affected)
Execution Time : 00:21:08:350
Transfer Time  : 00:00:00:000
Total Time    : 00:21:08:350

(428304 row(s) affected)
Execution Time : 00:15:56:801
Transfer Time  : 00:00:00:000
Total Time    : 00:15:56:801

(401770 row(s) affected)
Execution Time : 00:17:16:252
Transfer Time  : 00:00:00:000
Total Time    : 00:17:16:252

(378640 row(s) affected)
Execution Time : 00:24:00:030
Transfer Time  : 00:00:00:000
Total Time    : 00:24:00:030

(396594 row(s) affected)
Execution Time : 00:25:21:478
Transfer Time  : 00:00:00:000
Total Time    : 00:25:21:478

(265731 row(s) affected)
Execution Time : 00:11:04:330
Transfer Time  : 00:00:00:000
Total Time    : 00:11:04:330

作者: zhgc 时间: 2022-4-4 22:49
<?php

//两个全局变量
$dbhost="192.168.1.233";
//$table="r_sn_detail_h";
$table="r_sn_detail_t_20210501e";

//引用库文件
require_once("movedb.phar");
require_once("phar://movedb.phar/movedb.php");

echo version();

$tick=getTickCount();
//echo move_sn("123");
$sns=get_sn(100000,"","");
if(is_array($sns)){
if($sns){
foreach($sns as $sn=>$k){
echo move_sn($sn);
echo "\r\n";
}
}else{
echo "no sn found!\r\n";
}
}else{
echo $sns;
}
$tick=getTickCount()-$tick;
echo "[ Execute Time: {$tick} ]\r\n";

欢迎光临深圳全飞鸿 (http://www.nagomes.com/disc/)