Improve perfromance of my package

From: C Chang <cschang_at_maxinter.net>
Date: Wed, 26 Jun 2002 22:26:48 -0400
Message-ID: <3D1A77E8.354C@maxinter.net>

Hi Daniel and Other significants:

   I post the Package that I use the view of a union of 6 tables - VP$list_data_all_view. This is actually a synonym based on the real view(list_data_all_view) on another schema using a remote link to connect. Actually all name prefixed with vp$.. here are remote objects. I put my best to put these together. The return of few up to few tens of records is quick by this package called from a ASP, that use the MS ODBC for Oracle, DNS-less connection. However when it return 1000 records and above, it took about 2 minutes upto 4 mniutes for 3625 records back to a HTML format, and even worse when return back to an excel format. By watching the asp responses, it seems that the delay is due to this package. Can you suggest any improvement? I am thinking combining the c_getPoInfo and c_qty_rcvd into one cursor, probably combining the three table (po_heads, po_lines, receipts). Welcome anyone's suggestion. My system is on IIS 4 (separate web server), NT 4 with ORacle 8.1.6 , 1024M ram 700Mhz single PIII CPU. I am learning the use of trace of explain for this, meantime.

CREATE OR REPLACE PACKAGE vd2_Report AS

• Vendor Consumption Report Arrays TYPE tbl_counter IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE tbl_vendor IS TABLE OF vp$contract_status.vendor_id%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_nsn IS TABLE OF VARCHAR2(13) INDEX BY BINARY_INTEGER; TYPE tbl_contract_qty IS TABLE OF vp$contract_status.qty%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_um IS TABLE OF vp$contract_status.um%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_qty_ordered IS TABLE OF po_lines.qty%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_source_list IS TABLE OF vp$contract_status.source_list%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_source_lin IS TABLE OF vp$contract_status.source_lin%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_qty_period IS TABLE OF vp$contract_status.qty_period%TYPE INDEX BY BINARY_INTEGER; TYPE tbl_ref_date IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; TYPE tbl_all_sites IS TABLE OF sites.dflt_contract_id%TYPE INDEX BY BINARY_INTEGER;

PROCEDURE getVendorConsumption
(

	p_site_id   		IN 	VARCHAR2,   
	p_vendor			IN	VARCHAR2,
	p_niin			IN	VARCHAR2,
	o_count			IN OUT  NOCOPY tbl_counter,
	o_vendor			IN OUT  NOCOPY tbl_vendor,
	o_nsn				IN OUT  NOCOPY tbl_nsn,
	o_contract_qty		IN OUT  NOCOPY tbl_contract_qty,
	o_um				IN OUT  NOCOPY tbl_um,
	o_qty_ordered		IN OUT  NOCOPY tbl_qty_ordered,
	o_percentage		IN OUT  NOCOPY tbl_contract_qty,
	o_source_list		IN OUT  NOCOPY tbl_source_list,
	o_source_lin		IN OUT  NOCOPY tbl_source_lin,
	o_qty_period		IN OUT  NOCOPY tbl_qty_period,
	o_ref_date			IN OUT  NOCOPY tbl_ref_date,
	o_contract_id		IN OUT  NOCOPY tbl_all_sites,
	o_qty_rcvd              IN OUT  NOCOPY tbl_qty_ordered

);

END vd2_Report;
/
CREATE OR REPLACE PACKAGE BODY vd2_Report AS

PROCEDURE getVendorConsumption
 (

	p_site_id   		IN 		VARCHAR2,   
	p_vendor			IN		VARCHAR2,
	p_niin			IN		VARCHAR2,
	o_count			IN OUT	NOCOPY tbl_counter,
	o_vendor			IN OUT      NOCOPY tbl_vendor,
	o_nsn				IN OUT	NOCOPY tbl_nsn,
	o_contract_qty		IN OUT	NOCOPY tbl_contract_qty,
	o_um				IN OUT	NOCOPY tbl_um,
	o_qty_ordered		IN OUT      NOCOPY tbl_qty_ordered,
	o_percentage		IN OUT      NOCOPY tbl_contract_qty,
	o_source_list		IN OUT	NOCOPY tbl_source_list,
	o_source_lin		IN OUT	NOCOPY tbl_source_lin,
	o_qty_period		IN OUT	NOCOPY tbl_qty_period,
	o_ref_date			IN OUT      NOCOPY tbl_ref_date,
	o_contract_id		IN OUT      NOCOPY tbl_all_sites,
	o_qty_rcvd              IN OUT      NOCOPY tbl_qty_ordered

 )
 IS

	v_count			NUMBER DEFAULT 0;
      v_sqlstmt		VARCHAR2(2000);
      v_counter		NUMBER DEFAULT 0;
	v_index		NUMBER DEFAULT 0;
	v_length		NUMBER DEFAULT 0;

	v_vendor		vp$contract_status.vendor_id%TYPE;
	v_nsn			VARCHAR2(13);
	v_contract_qty	vp$contract_status.qty%TYPE;
	v_um			vp$contract_status.um%TYPE;
	v_qty_ordered	po_lines.qty%TYPE;
	v_contractID	sites.dflt_contract_id%TYPE;
	v_percentage	vp$contract_status.qty%TYPE;
	v_source_list	vp$contract_status.source_list%TYPE;
	v_source_lin      vp$contract_status.source_lin%TYPE;
	v_qty_period	vp$contract_status.qty_period%TYPE;
	v_niin	      po_lines.niin%TYPE;
	v_qty_rcvd        receipts.qty%TYPE;
	v_ref_date		VARCHAR2(14);
	v_sites		VARCHAR2(70);
	v_siteContract    VARCHAR2(70);
	
	TYPE  cursor_T IS REF CURSOR;
   	v_cursor    cursor_T;
	v_siteList	tbl_all_sites;
	


	-- dummy variables for fixing Oracle Uninitializing errors

	t_count			tbl_counter;
	t_vendor		tbl_vendor;
	t_nsn			tbl_nsn;
	t_contract_qty	tbl_contract_qty;
	t_um			tbl_um;
	t_qty_ordered	tbl_qty_ordered;
	t_percentage	tbl_contract_qty;
	t_source_list	tbl_source_list;
	t_source_lin	tbl_source_lin;
	t_qty_period	tbl_qty_period;
	t_ref_date		tbl_ref_date;
	t_contract_id	tbl_all_sites;
	t_qty_rcvd   	tbl_qty_ordered;
	
		
	CURSOR c_getContractID(p_site IN sites.site_id%TYPE) IS
		SELECT dflt_contract_id
		FROM sites 
		WHERE site_id = p_site;
	


	-- This cursor is used to get the sum of the quantity ordered for niin

	CURSOR c_getPoInfo(p_niin IN po_lines.NIIN%TYPE,
				       p_date IN VARCHAR2,
				       p_vendor IN catalog_items.vendor_id%TYPE, 
				       p_site   IN sites.site_id%TYPE) IS
	
		SELECT SUM(p.qty)
		FROM po_heads ph,po_lines p
		WHERE ph.site_id= p_site
		AND TRUNC(ph.po_date) > TRUNC(TO_DATE(p_date,'MM/DD/YYYY'))
		AND ph.vendor_id = p_vendor
		AND ph.po_id=p.po_id
		AND niin = p_niin;
		

• This cursor is used to get the sum of the quantity received for niin at specified site CURSOR c_qty_rcvd ( i_niin IN receipts.niin%TYPE, i_rcvd_date IN VARCHAR2, i_site IN receipts.site_id%TYPE ) IS SELECT SUM(qty) FROM receipts WHERE site_id= i_site AND TRUNC(rcvd_date) > TRUNC(TO_DATE(i_rcvd_date,'MM/DD/YYYY')) AND SAMMS_FLAG = 'N' AND niin = i_niin;
  • This cursor gets the reference date which is the original contract date CURSOR c_getRefDate (p_source_list IN vp$source_lists.list%TYPE) IS SELECT DECODE(reference_date,NULL, TO_CHAR (source_file_date,'MM/DD/YYYY'),TO_CHAR(reference_date,'MM/DD/YYYY')) FROM vp$source_lists WHERE list = p_source_list AND price_type = 'S';
    • This cursor gets the contracted qty and um from list_data_all_view of other schema CURSOR c_list_data_all ( i_niin IN VP$list_data_all_view.niin%TYPE, i_source_list IN VP$list_data_all_view.list%TYPE) IS SELECT qty, um FROM VP$list_data_all_view WHERE niin = i_niin AND list = i_source_list;

	BEGIN
		v_sites:= p_site_id;
		v_length:= LENGTH(p_site_id);		
		IF v_length > 3 THEN
		
			FOR v_counter IN 0..(v_length/3) LOOP
				v_index:= INSTR(v_sites,','); -- find first occurence
				v_siteList(v_counter):=SUBSTR(v_sites,1,v_index-1);
				v_sites:=SUBSTR(v_sites,v_index+1);
			END LOOP;
		
			v_sites:=NULL;
			v_counter:=0;
			v_siteContract:=NULL;
		
		
			FOR v_counter IN 0..(v_length/3) LOOP 
				OPEN c_getContractID(v_siteList(v_counter));
				FETCH c_getContractID
				INTO v_sites;
				EXIT WHEN c_getContractID%NOTFOUND;
		        v_siteContract:=   v_siteCOntract || ',' ||'''' || v_sites
||'''' ;
				v_sites:=NULL;
				CLOSE c_getContractID;
			END LOOP;

			v_siteContract:= SUBSTR(v_siteContract,2);
			
		ELSE
			OPEN c_getContractID(p_site_id);
			FETCH c_getContractID
			INTO v_sites;
			v_siteContract:= v_sites;
	
		END IF;
		
		v_sqlstmt:= 'SELECT ci.vendor_id,ci.fsc||ci.niin,';
	v_sqlstmt:= v_sqlstmt
||ci.vendor_list,c.vendor_lin,c.qty_period,ci.contract_id';
		v_sqlstmt:= v_sqlstmt || ' FROM vp$contract_status c, catalog_items

ci';                 

		IF LENGTH(p_site_id) > 3 THEN
	v_sqlstmt:= v_sqlstmt || ' WHERE ci.contract_id IN (' ||  
v_siteContract || ')';
		ELSE
	v_sqlstmt:= v_sqlstmt || ' WHERE ci.contract_id =' ||''''||
v_siteContract ||'''';
		END IF;
		
		IF p_vendor IS NOT NULL THEN
		v_sqlstmt:= v_sqlstmt || ' AND ci.vendor_id=' ||'''' || p_vendor ||
'''' ;
		END IF;
		
		IF p_niin IS NOT NULL THEN
			v_sqlstmt:= v_sqlstmt || ' AND ci.niin=' ||'''' || p_niin || '''' ;
		END IF;
		
		v_sqlstmt:= v_sqlstmt || ' AND ci.vendor_id <>  ';
		v_sqlstmt:= v_sqlstmt || '''' || vd2_util.SAMMS_ORDER || '''' ;
		v_sqlstmt:= v_sqlstmt || ' AND ci.contract_id=c.contract ';
		v_sqlstmt:= v_sqlstmt || ' AND ci.niin=c.niin ';
		
		-- initialize table index
		o_vendor:=t_vendor;
		o_nsn:=t_nsn;
		o_contract_qty:=t_contract_qty;
		o_um:=t_um;
		o_qty_ordered:=t_qty_ordered;
		o_qty_rcvd:=t_qty_rcvd;
		o_percentage:=t_percentage;
		o_source_list:=t_source_list;
		o_source_lin:=t_source_lin;
		o_qty_period:=t_qty_period;
		o_ref_date:=t_ref_date;
		o_contract_id:=t_contract_id;
        	
		OPEN v_cursor FOR v_sqlstmt;
			LOOP
			
				FETCH v_cursor
				INTO v_vendor,v_nsn,v_source_list,
				v_source_lin,v_qty_period,v_contractId;
				EXIT WHEN v_cursor%NOTFOUND;
			
				v_count:= v_count + 1; 
						
				-- not all of the NSN have an FSC so check on the length first
				IF length(v_nsn) < 13 THEN
					v_niin:= v_nsn;
				ELSE
					v_niin:=substr(v_nsn,5,9);
				END IF;
				
                OPEN c_list_data_all(v_niin, v_source_list);
                FETCH c_list_data_all
                INTO v_contract_qty, v_um;
                CLOSE  c_list_data_all;
              
				o_vendor(v_count):= v_vendor;
				o_nsn(v_count):= v_nsn;
				o_contract_qty(v_count):=v_contract_qty;
				o_um(v_count):=v_um;
				o_source_list(v_count):=v_source_list;
				o_source_lin(v_count):=v_source_lin;
				o_qty_period(v_count):=v_qty_period;
				o_contract_id(v_count):=v_contractid;
				
				OPEN c_getRefDate(v_source_list);
				FETCH c_getRefDate 
				INTO v_ref_date;
				CLOSE c_getRefDate;
			
				o_ref_date(v_count):=v_ref_date;
				
			OPEN
c_getPoInfo(v_niin,v_ref_date,v_vendor,SUBSTR(v_contractid,1,2));
				FETCH c_getPOInfo
				INTO v_qty_ordered;
    			CLOSE c_getPoInfo;
    			
				o_qty_ordered(v_count):=v_qty_ordered;
				
			    OPEN c_qty_rcvd(v_niin,v_ref_date,SUBSTR(v_contractid,1,2));
			    FETCH c_qty_rcvd
			    INTO v_qty_rcvd;
			    CLOSE c_qty_rcvd;
			    
				IF (v_qty_rcvd IS NOT NULL) THEN
					o_qty_rcvd(v_count):=v_qty_rcvd;
					IF v_contract_qty <> 0 THEN
					  o_percentage(v_count):=((v_qty_rcvd/v_contract_qty)*100);
					ELSE
					   o_percentage(v_count):= 0;
					END IF; 
				ELSE
					o_qty_rcvd(v_count):= 0;
					o_percentage(v_count):= 0;													
				END IF;
			
				v_vendor:=NULL;
				v_nsn:=NULL;
				v_contract_qty:=NULL;
				v_um:=NULL;
				v_qty_ordered:=NULL;
				v_qty_rcvd:=NULL;
				v_contractID:=NULL;
				v_percentage:=NULL;
				v_source_list:=NULL;
				v_source_lin:=NULL;
				v_qty_period:=NULL;
				v_ref_date:=NULL;
			END LOOP;	

		CLOSE v_cursor;		
		o_count(1):= v_count;
		

END getVendorConsumption;
END vd2_Report;
/ Received on Wed Jun 26 2002 - 21:26:48 CDT