达梦数据库使用游标进行单行获取同BULK COLLECT INTO 批量获取效率对比

1. 数据准备

表5张。数据量分别为1W,10W,100W,1000W,1亿。

DECLARE
TABNAME VARCHAR(20);
SCHNAME VARCHAR(20);
TABSIZE INTEGER;
V_SQL VARCHAR(8188);
V_SQL1 VARCHAR(8188);
CURSOR C1;
BEGIN
	--SELECT TABLE_NAME INTO TBNAME FROM DBA_TABLES WHERE OWNER = 'DMTEST' AND TABLE_NAME LIKE '%TB%';
	OPEN C1 FOR 'SELECT T1.NAME AS SCHNAME, T2.NAME AS TABNAME FROM SYSOBJECTS T1, SYSOBJECTS T2 WHERE T1.NAME = ''DMTEST'' AND T1.TYPE$=''SCH''' ||
	'AND T2.NAME LIKE ''%TB%'' AND T2.TYPE$=''SCHOBJ'' AND T2.SUBTYPE$=''UTAB'' AND T1.ID = T2.SCHID ';
	LOOP
		FETCH C1 INTO SCHNAME,TABNAME;
		EXIT WHEN C1%NOTFOUND;
		V_SQL1 = 'SELECT COUNT(*) FROM ' || SCHNAME || '.' || TABNAME;
		EXECUTE IMMEDIATE V_SQL1 INTO TABSIZE;
		PRINT 'SCHNAME: ' || SCHNAME || ', TABNAME: ' || TABNAME || ', SIZE: ' || TABSIZE;
	END LOOP;
	CLOSE C1;
END;

 2. 性能测试

2.1 1W数据测试

单行读取：

--单行 数据量10000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT * FROM DMTEST.TB1;
	LOOP
		FETCH C1 INTO RET;
		EXIT WHEN C1%NOTFOUND;
		NULL;
	END LOOP;
	CLOSE C1;
END;

 耗时85毫秒

多行读取：

--多行 数据量10000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT  * FROM DMTEST.TB1;
	LOOP
		FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
		EXIT WHEN C1%NOTFOUND;
		FOR I IN 1..RET.COUNT LOOP
			NULL;
		END LOOP;
		/*
		FOR I IN RET.FIRST..RET.LAST LOOP
			NULL;
		END LOOP;
		*/
	END LOOP;
	CLOSE C1;
END;

耗时6毫秒

2.2 10W 性能测试

单行读取：

--单行 数据量100000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT * FROM DMTEST.TB2;
	LOOP
		FETCH C1 INTO RET;
		EXIT WHEN C1%NOTFOUND;
		NULL;
	END LOOP;
	CLOSE C1;
END;

耗时80毫秒

多行读取：

--多行 数据量100000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT  * FROM DMTEST.TB2;
	LOOP
		FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
		EXIT WHEN C1%NOTFOUND;
		FOR I IN 1..RET.COUNT LOOP
			NULL;
		END LOOP;
		/*
		FOR I IN RET.FIRST..RET.LAST LOOP
			NULL;
		END LOOP;
		*/
	END LOOP;
	CLOSE C1;
END;

 耗时34毫秒

2.3 100W性能测试

单行读取：

--单行 数据量1000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT * FROM DMTEST.TB3;
	LOOP
		FETCH C1 INTO RET;
		EXIT WHEN C1%NOTFOUND;
		NULL;
	END LOOP;
	CLOSE C1;
END;

耗时634毫秒

多行读取：

DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT  * FROM DMTEST.TB3;
	LOOP
		FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
		EXIT WHEN C1%NOTFOUND;
		FOR I IN 1..RET.COUNT LOOP
			NULL;
		END LOOP;
		/*
		FOR I IN RET.FIRST..RET.LAST LOOP
			NULL;
		END LOOP;
		*/
	END LOOP;
	CLOSE C1;
END;

 耗时251毫秒

2.4 1000W性能测试

单行读取：

--单行 数据量10000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT * FROM DMTEST.TB4;
	LOOP
		FETCH C1 INTO RET;
		EXIT WHEN C1%NOTFOUND;
		NULL;
	END LOOP;
	CLOSE C1;
END;

 耗时7秒277毫秒

多行读取：

DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT  * FROM DMTEST.TB4;
	LOOP
		FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
		EXIT WHEN C1%NOTFOUND;
		FOR I IN 1..RET.COUNT LOOP
			NULL;
		END LOOP;
		/*
		FOR I IN RET.FIRST..RET.LAST LOOP
			NULL;
		END LOOP;
		*/
	END LOOP;
	CLOSE C1;
END;

耗时2秒559毫秒

2.5 1亿性能测试

单行读取：

--单行 数据量100000000
DECLARE
RET TB1%ROWTYPE;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT * FROM DMTEST.TB5;
	LOOP
		FETCH C1 INTO RET;
		EXIT WHEN C1%NOTFOUND;
		NULL;
	END LOOP;
	CLOSE C1;
END;

 耗时1分22秒170毫秒

多行读取：

--多行 数据量100000000
DECLARE
TYPE ARR IS TABLE OF DMTEST.TB1%ROWTYPE;
RET ARR;
CURSOR C1;
BEGIN
	OPEN C1 FOR SELECT  * FROM DMTEST.TB5;
	LOOP
		FETCH C1 BULK COLLECT INTO RET LIMIT 3000;
		EXIT WHEN C1%NOTFOUND;
		FOR I IN 1..RET.COUNT LOOP
			NULL;
		END LOOP;
		/*
		FOR I IN RET.FIRST..RET.LAST LOOP
			NULL;
		END LOOP;
		*/
	END LOOP;
	CLOSE C1;
END;

耗时1分8秒49毫秒

3 总结

        上述实验可看出数据量从1W-1亿量级。对于表数据的遍历。批量读取的效率都高于单行读取。底层原因为当运行一个pl/sql程序时， pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句，它会把这个语句传给sql引擎（后台发生上下文切换）。在PL/SQL和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。缺点为会消耗更多的内存(PGA)。由于该数集合据存储在每个会话中,假设一个会话多消耗5M，内存，那么1000个就消耗约5G内存。