Function took long time for execution..

[kiranrajenimbalkar]

Hi below function taking long time when i am going to execute this function.

CREATE OR REPLACE FUNCTION Getdistrictforu ser(PID IN WEBUSER.ID%TYPE )
RETURN VARCHAR2 IS
VCLUB VARCHAR(1000);

CURSOR CURSOROUT(PID VARCHAR2) IS
SELECT DISTINCT AD.DISTRICTID
FROM MEMBER M
JOIN CLUB C ON M.RCLBID = C.ID
AND M.RCLBIDTYPE = C.IDTYPE
AND M.MEMSTATCD IN (0, 51)
AND C.CLUBSTATCD < 21
JOIN ACTIVECLUBDIST AD ON AD.ID = C.ID
AND AD.IDTYPE = C.IDTYPE
AND M.ID = PID
JOIN ACTIVERIZONE AZ ON AD.DISTRICTID = AZ.DISTRICTID
AND C.CNTRYID = AZ.CNTRYID;
BEGIN
FOR REC_CUR IN CURSOROUT(PID) LOOP
IF VCLUB IS NULL THEN
VCLUB := REC_CUR.DISTRIC TID;
ELSE
VCLUB := VCLUB || ', ' || REC_CUR.DISTRIC TID;
END IF;
END LOOP;

RETURN VCLUB;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT ('error');
END;
END GETDISTRICTFORU SER;

in the above SQL two views been used

1)ACTIVECLUBDIS T
2)ACTIVERIZONE

First view ACTIVECLUBDIST contains this sql

SELECT "ID","IDTYPE"," DISTRICTID","EF FDT","MODDT","M ODUSERID","ENDD T" FROM ClubDist
WHERE EffDt <= SYSDATE AND
(EndDt >= SYSDATE OR
EndDt IS NULL)and Second view ACTIVERIZONE contains this sql

SELECT cntryid,distric tid,rizoneid,re gionid,moddt,mo duserid,effdt,e nddt FROM Rizone
WHERE EffDt <= SYSDATE
AND(EndDt >= SYSDATE OR EndDt IS NULL).

because i am calling this function from one SQL so due to this its took long time.

so please request you that provide me better solution or modifie this function for better performance.

Regards,
Kiran

[OraMaster]

You need to add RETURN in also exception section of your SP.

[Madhusmita Biswal]

Poor performance - Issue

Depending on how much data is in the tables, you may need to place indexes on the columns that are being joined against. Often slow querying speed comes down to lack of an index in the right place.

The issue mainly is in the multiple join statment. If possible try to avoid join

Read these article