用存储过程更新30万条数据,用了7个小时,有没有更好的方法

开源中国-总书记 发布于 2016/07/20 17:29
阅读 1K+
收藏 3
CREATE OR REPLACE 
PROCEDURE P_REPORT_CUST_VALID IS 
	RETAIL_NUM NUMBER ; 
	ENGINEER_NUM NUMBER ; 
	NUM NUMBER ; 
	CUSTOMER_INFO_ID NUMBER ; 
	J NUMBER ;  
CURSOR SHOP_NUM IS SELECT * FROM CUST_SHOP_INFO_DETAIL ;
BEGIN
	J := 1 ;  
	FOR C_SHOP_NUM IN SHOP_NUM LOOP 
	J := J + 1 ;
 SELECT
		 nvl(SUM(C.RETAIL_NUM),0)  r INTO RETAIL_NUM
	FROM
		PSI_SALES_C C
	WHERE
		C.CUST_SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
	AND TO_CHAR (
		C.CREATED_DATE,
		'yyyy-MM-dd'
	) >= TO_CHAR (
		ADD_MONTHS (SYSDATE ,- 3),
		'yyyy-MM-dd'
	) ; 

SELECT
		 nvl(SUM(C.ENGINEER_NUM),0)  E INTO ENGINEER_NUM
	FROM
		PSI_SALES_C C
	WHERE
		C.CUST_SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
	AND TO_CHAR (
		C.CREATED_DATE,
		'yyyy-MM-dd'
	) >= TO_CHAR (
		ADD_MONTHS (SYSDATE ,- 3),
		'yyyy-MM-dd'
	) ; 

SELECT
		nvl(b.CUSTOMER_INFO_ID,0) sd INTO CUSTOMER_INFO_ID
	FROM
		CUST_SHOP_INFO b
	WHERE
		b.SHOP_INFO_ID = (
			SELECT
				A .SHOP_ID
			FROM
				CUST_SHOP_INFO_DETAIL A
			WHERE
				A .SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
		) ;
SELECT
			NVL (SUM(D .NUM), 0) INTO NUM
		FROM
			PSI_SALES_B D
		WHERE
			D .FH_CUSTOMER_ID = CUSTOMER_INFO_ID
		AND TO_CHAR (
			D .CREATED_DATE,
			'yyyy-MM-dd'
		) >= TO_CHAR (
			ADD_MONTHS (SYSDATE ,- 3),
			'yyyy-MM-dd'
		) ;
		IF (
			RETAIL_NUM = 0
			AND ENGINEER_NUM = 0 
			AND NUM = 0 
 		) THEN
			UPDATE CUST_SHOP_INFO_DETAIL csid
		SET CSID.IS_VALID = '1'
		WHERE
			CSID.SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID ;
		ELSE
			UPDATE CUST_SHOP_INFO_DETAIL csid
		SET CSID.IS_VALID = '0'
		WHERE
			CSID.SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID ;
		END IF ;
		END LOOP ;
COMMIT ;
		END P_REPORT_CUST_VALID ;

更新1000条数据大概三分钟,该怎么修改?


加载中
0
开源中国-总书记
开源中国-总书记
有没有大神,会的
0
wad12302
wad12302

首先你的

CURSOR SHOP_NUM IS SELECT * FROM CUST_SHOP_INFO_DETAIL ; 这个语句有问题, 你这样是全部一次性加载所有数据,不够效率,你可以使用分页,或者循环查询,你先试试只查询 200条一次,然后循环更新耗时多久,都可以打印日志

开源中国-总书记
开源中国-总书记
怎么循环查询?不太懂
0
烟雨三月
烟雨三月
分页批量更新数据提交事务。没有必要使用 RETAIL_NUM 这些中间变量,大概看了下,这些都可以作为最外层 CURSOR SHOP_NUM IS SELECT * FROM CUST_SHOP_INFO_DETAIL 的筛选条件。如果日期列是日期类型不是varchar类型,最好不要使用to_char转换成字符串比较,直接使用日期类型进行比较。你可以大概介绍下主要逻辑,其他人理解起来方便些。
烟雨三月
烟雨三月
回复 @开源中国-总书记 : 用一个变量记录处理了多少条数据,每 100(可以根据实际处理速度进行调整) 条提交事务。
开源中国-总书记
开源中国-总书记
如何分页提交事物?
0
blankHeart
blankHeart

游标SHOP_NUM只用了SHOP_DETAIL_ID字段,那就只查询这一个字段就行了

LOOP中的前两条查询语句是对同一张表操作的,写到一起

where .. and .. 比较and连接的两部分哪部分查询起来快,快的放前面

比较日期不要to_char

0
魔力猫
魔力猫


我就不说别的。

TO_CHAR (

        C.CREATED_DATE,
        'yyyy-MM-dd'
    ) >= TO_CHAR (
        ADD_MONTHS (SYSDATE ,- 3),
        'yyyy-MM-dd'
    ) ;

这个SQL效率得有多糟糕。而且你需要把所有的记录都走一遍游标再判断吗?这么多全表扫描,真是要死人了。

0
jolphin
jolphin

你应该把业务需求和场景说清楚吧, 各个表之间的关系,还有数据量多少等。
你用写程序的思维去写SQL,用游标去循环30万次,性能肯定上不去。

开源中国-总书记
开源中国-总书记
下面刚写
0
ToBeHJH
ToBeHJH
一条SQL就可以解决的事,非要用游标去处理。就算你游标里的SQL执行效率再高,循环30万次也要慢得跟蜗牛一样
开源中国-总书记
开源中国-总书记
对每条数据进行计算,如何用一条sql写出来?
0
开源中国-总书记
开源中国-总书记
CREATE OR REPLACE 
PROCEDURE P_REPORT_CUST_VALID IS 
	RETAIL_NUM NUMBER ; 
	ENGINEER_NUM NUMBER ; 
	NUM NUMBER ; 
	CUSTOMER_INFO_ID NUMBER ; 
	J NUMBER ;  
CURSOR SHOP_NUM IS SELECT CS.SHOP_DETAIL_ID FROM CUST_SHOP_INFO_DETAIL cs;
BEGIN
	J := 1 ;  
	FOR C_SHOP_NUM IN SHOP_NUM LOOP 
	J := J + 1 ;
--计算零售量
 SELECT
		 nvl(SUM(C.RETAIL_NUM),0)  r INTO RETAIL_NUM
	FROM
		PSI_SALES_C C
	WHERE
		C.CUST_SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
	AND   C.CREATED_DATE >= ADD_MONTHS (SYSDATE ,- 3);
		 
--计算工程量
SELECT
		 nvl(SUM(C.ENGINEER_NUM),0)  E INTO ENGINEER_NUM
	FROM
		PSI_SALES_C C
	WHERE
		C.CUST_SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
	AND C.CREATED_DATE >= ADD_MONTHS (SYSDATE ,- 3);
--计算批发量
SELECT
		nvl(b.CUSTOMER_INFO_ID,0) sd INTO CUSTOMER_INFO_ID
	FROM
		CUST_SHOP_INFO b
	WHERE
		b.SHOP_INFO_ID = (
			SELECT
				A .SHOP_ID
			FROM
				CUST_SHOP_INFO_DETAIL A
			WHERE
				A .SHOP_DETAIL_ID = C_SHOP_NUM.SHOP_DETAIL_ID
		) ;
	--SELECT
	--		NVL (SUM(D .NUM), 0) INTO NUM
	--	FROM
	--		PSI_SALES_B D
	--	WHERE
	--		D .FH_CUSTOMER_ID = CUSTOMER_INFO_ID
	--	AND  D .CREATED_DATE  >=   ADD_MONTHS (SYSDATE ,- 3) ;
		IF (
			RETAIL_NUM = 0
			AND ENGINEER_NUM = 0 
			--AND NUM = 0 
 		) THEN
			 INSERT INTO CUST_VALID(CUST_SHOP_D_ID,IS_VALID)VALUES(C_SHOP_NUM.SHOP_DETAIL_ID,'1');
		ELSE
			INSERT INTO CUST_VALID(CUST_SHOP_D_ID,IS_VALID)VALUES(C_SHOP_NUM.SHOP_DETAIL_ID,'0');
		END IF ;
		IF j>1000
			THEN 
				return;
		END IF;
		END LOOP ;
COMMIT ;

END P_REPORT_CUST_VALID ;


我这个场景是  有一个表CUST_SHOP_INFO_DETAIL 大概30万条数据,根据需求,我要每一条都要判断是否有效,而计算是否有效,就要查询PSI_SALES_C 和 PSI_SALES_B 对应的数据进行判断,我通过测试,就是在查询PSI_SALES_B 这个表的时候,速度立马降下来了,我把它注释掉,时间只用0.68秒,如果加上注释的部分,大概一分钟,不过PSI_SALES_C 大概1600万条数据,而PSI_SALES_B 大概仅300万条数据。




开源中国-总书记
开源中国-总书记
我把更新变为新插入到一个表中
0
lgscofield
lgscofield

把游标改一下,存储过程中出现游标是个大问题,你可以在游标的地方加上:

v_begintime:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  // 你的逻辑sql
v_endtime:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

试试就出来了,哪里比较耗时

0
乌龟壳
乌龟壳
不去学习使用查询计划分析性能,使用统计信息分析性能,帮你解决再多这样的问题都是白搭。
返回顶部
顶部