Home » SQL & PL/SQL » SQL & PL/SQL » Increase Pipeline Performance
Increase Pipeline Performance [message #211946] Wed, 03 January 2007 03:17 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
Hello

I've managed to get this function to work fine now :

CREATE OR REPLACE PACKAGE BODY PARSE_ATTENDANCE AS 

FUNCTION ENUM_MARKS(C_CURSOR IN SYS_REFCURSOR) 
RETURN TMP_ATT_DATA_TBL PIPELINED
IS

T_ROW		TMP_ATT_HOLDING:=TMP_ATT_HOLDING(NULL, NULL, NULL, NULL);
T_STUD 		NUMBER(10);
T_BASE 		NUMBER(10);
T_DATE 		DATE;
T_MARKS 	VARCHAR2(1000);
LEN_MARKS 	NUMBER;
PDATE		DATE;
SDATE		DATE;
EDATE		DATE;
SLEN 		NUMBER;
WEEKLEN 	NUMBER;
INIPOS 		NUMBER;
MARRAY 		VARCHAR2(1000);
SUBARRAY 	SARRAY := SARRAY();
SFILL 		VARCHAR2(14) := '--------------';
EPOS 		NUMBER;
	
BEGIN

SUBARRAY.EXTEND(17);

LOOP
	FETCH C_CURSOR INTO T_ROW.STUD_ID, T_ROW.BASE_ID, T_ROW.START_DATE, 	T_ROW.MARKS;

	EXIT WHEN C_CURSOR%NOTFOUND;

T_STUD := T_ROW.STUD_ID;
T_BASE := T_ROW.BASE_ID;
T_DATE := TO_DATE(T_ROW.START_DATE, 'DD/MM/YYYY');
T_MARKS := T_ROW.MARKS;

LEN_MARKS := LENGTH(T_MARKS);
EPOS := LEN_MARKS / 2;
SDATE := ROUND(TO_DATE(T_DATE), 'W') - 1;
INIPOS := TO_NUMBER(TO_CHAR(T_DATE, 'D'));
SLEN := INIPOS + 3;
PDATE := SDATE;
EDATE := SDATE + EPOS;

MARRAY := SUBSTR(T_MARKS, 1, SLEN);
WEEKLEN := LENGTH(MARRAY);

IF WEEKLEN < 14 THEN 
	MARRAY := SUBSTR(SFILL, 1, 14 - WEEKLEN) || MARRAY;
END IF;

SUBARRAY(1) := T_STUD;
SUBARRAY(2) := T_BASE;
SUBARRAY(3) := PDATE;

FOR i IN 4 .. 17 LOOP
	SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
END LOOP;

PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(1),SUBARRAY(2),SUBARRAY(3),SUBARRAY(4),
	SUBARRAY(5),SUBARRAY(6),SUBARRAY(7),SUBARRAY(8),SUBARRAY(9),
	SUBARRAY(10),SUBARRAY(11),SUBARRAY(12),SUBARRAY(13),SUBARRAY(14),
	SUBARRAY(15),SUBARRAY(16),SUBARRAY(17)));

	PDATE := PDATE + 7;

WHILE PDATE <= EDATE LOOP

 	MARRAY := SUBSTR(T_MARKS, SLEN + 1, 14);

	WEEKLEN := LENGTH(MARRAY);

	IF WEEKLEN < 14 THEN 
		MARRAY := MARRAY || SUBSTR(SFILL, 1, 14 - WEEKLEN);
	END IF;

	FOR i IN 4 .. 17 LOOP
		SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
	END LOOP;
	
	SUBARRAY(3) := PDATE;
	
	PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(1),SUBARRAY(2),SUBARRAY(3),SUBARRAY(4),
	SUBARRAY(5),SUBARRAY(6),SUBARRAY(7),SUBARRAY(8),SUBARRAY(9),
	SUBARRAY(10),SUBARRAY(11),SUBARRAY(12),SUBARRAY(13),SUBARRAY(14),
	SUBARRAY(15),SUBARRAY(16),SUBARRAY(17)));
	
	PDATE := PDATE + 7;
	SLEN := SLEN + 14; 

END LOOP;
END LOOP;
CLOSE C_CURSOR;
RETURN;
END ENUM_MARKS;
END PARSE_ATTENDANCE;


I've created a view wrapping this function, but it does take ages to run. Obviously this is due to memory usage and whatnot.

Is there a way to make this more efficient?

I've been reading about Bulk Fetch and For All interations, but had limited success.

Anyone got any pointers?

Thanks in advance
Re: Increase Pipeline Performance [message #211982 is a reply to message #211946] Wed, 03 January 2007 06:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What about the cursor that you pass in?

Run it via a SELECT with ROWNUM = 1. How long does it take? This is the time taken to execute the cursor.
Now run it for all rows. How long does it take?

Get the difference between these two numbers and divide it into the number of rows processed. This will give you a figure in rows per second. What is it?

The PL/SQL looks pretty efficient. My only sugestion would be to BULK COLLECT 1000 rows at a time, and investigate the efficiency of the cursor.

Ross Leishman
Previous Topic: Cursor in a sql statement
Next Topic: How many DML Performe for a spacific period
Goto Forum:
  


Current Time: Sun Dec 04 22:40:25 CST 2016

Total time taken to generate the page: 0.08732 seconds