By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,122 Members | 654 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,122 IT Pros & Developers. It's quick & easy.

Function took long time for execution..

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

CREATE OR REPLACE FUNCTION Getdistrictforuser(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.DISTRICTID;
ELSE
VCLUB := VCLUB || ', ' || REC_CUR.DISTRICTID;
END IF;
END LOOP;

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

in the above SQL two views been used

1)ACTIVECLUBDIST
2)ACTIVERIZONE

First view ACTIVECLUBDIST contains this sql

SELECT "ID","IDTYPE","DISTRICTID","EFFDT","MODDT","MODUSE RID","ENDDT" FROM ClubDist
WHERE EffDt <= SYSDATE AND
(EndDt >= SYSDATE OR
EndDt IS NULL)and Second view ACTIVERIZONE contains this sql

SELECT cntryid,districtid,rizoneid,regionid,moddt,moduser id,effdt,enddt 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
Feb 19 '10 #1
Share this Question
Share on Google+
2 Replies

OraMaster
100+
P: 135
You need to add RETURN in also exception section of your SP.
Feb 25 '10 #2

P: 6
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
Feb 26 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.