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

SQL Not working with the function

P: 13
Hi,

when i am executing the below SQL its taking long time for execution so please provide me the solution.

Expand|Select|Wrap|Line Numbers
  1. SELECT A.ID,
  2.        A.IDTYPE,
  3.        A.USERNAME,
  4.        A.FIRSTNAME,
  5.        A.LASTNAME,
  6.        A.STATUS,
  7.        A.LANGUAGEID,
  8.        A.LANGUAGENAME,
  9.        GETROLESFORUSER(A.USERNAME) ROLES,
  10.        0 CLUBID,
  11.        A.ISDISABLED,
  12.        0 REGION
  13.   FROM (SELECT W.ID,
  14.                W.IDTYPE,
  15.                W.USERNAME,
  16.                P.FIRSTNAME,
  17.                P.LASTNAME,
  18.                W.ACCT_STATCD STATUS,
  19.                W.PREFERREDLANGUAGE LANGUAGEID,
  20.                LT.LANGNAME LANGUAGENAME,
  21.                P.SORTFIRSTNAME,
  22.                P.SORTLASTNAME,
  23.                W.ISDISABLED,
  24.                TRUNC(W.CREATEDT) CREATEDT,
  25.                REGISTRATION_TYPE
  26.           FROM WEBUSER W
  27.           JOIN PERSON P ON W.ID = P.ID
  28.                        AND W.IDTYPE = P.IDTYPE
  29.           LEFT JOIN LANGTYPE LT ON W.PREFERREDLANGUAGE = LT.LANGTY) A
  30.  WHERE 1 = 1
  31.    AND TRUNC(A.CREATEDT) BETWEEN '01-JUN-2009' AND '30-Oct-2009';
In this SQL one function is being used GETROLESFORUSER and also i am executing above SQL I got the error function return without value..

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION GETROLESFORUSER(PUSERNAME IN WEBUSER.USERNAME%TYPE)
  2.  
  3.  RETURN VARCHAR2 IS
  4.  
  5.   VROLES VARCHAR2(32767);
  6.  
  7.   TYPE MY_ROLES IS TABLE OF VARCHAR2(100);
  8.  
  9.   MYROLE MY_ROLES;
  10.  
  11.   CURSOR CURSOROUT(PUSERNAME VARCHAR2) IS
  12.     SELECT NVL2(C.COMMDESC, PN.POSDESC || '-' || C.COMMDESC, PN.POSDESC) ROLENAME
  13.       FROM POSITIONHOLDER PH,
  14.            WEBUSER WU,
  15.            POSITIONNAME PN,
  16.            (SELECT CD.COMMID, CN.COMMDESC
  17.               FROM COMMITTEE CD, COMMNAME CN
  18.              WHERE CN.COMMID = CD.COMMID
  19.                AND CD.EFFDT <= SYSDATE
  20.                AND (CD.ENDDT >= SYSDATE OR CD.ENDDT IS NULL)
  21.                AND CN.LANGTY = 0) C
  22.      WHERE PH.ID = WU.ID
  23.        AND PH.IDTYPE = WU.IDTYPE
  24.        AND PH.POSITIONID = PN.POSITIONID
  25.        AND PH.COMMID = C.COMMID(+)
  26.        AND PH.EFFDT <= SYSDATE
  27.        AND (PH.ENDDT >= SYSDATE OR PH.ENDDT IS NULL)
  28.        AND UPPER(WU.USERNAME) = UPPER(PUSERNAME)
  29.        AND PN.LANGTY = 0
  30.        AND PN.GENDER = 'M'
  31.     UNION
  32.     SELECT PN.POSDESC
  33.       FROM POSITIONNAME PN
  34.      WHERE PN.POSDESC = 'All Constituents';
  35.  
  36. BEGIN
  37.  
  38.   OPEN CURSOROUT(PUSERNAME);
  39.  
  40.   FETCH CURSOROUT BULK COLLECT INTO MYROLE;
  41.  
  42.   FOR REC_CUR IN MYROLE.FIRST .. MYROLE.LAST LOOP
  43.  
  44.     IF MYROLE(REC_CUR) IS NOT NULL THEN
  45.  
  46.       VROLES := VROLES || ', ' || MYROLE(REC_CUR);
  47.  
  48.     END IF;
  49.  
  50.   END LOOP;
  51.  
  52.   VROLES := SUBSTR(VROLES, 3);
  53.  
  54.   RETURN VROLES;
  55.  
  56. EXCEPTION
  57.   WHEN OTHERS THEN
  58.     BEGIN
  59.       DBMS_OUTPUT.PUT('error');
  60.     END;
  61. END GETROLESFORUSER;
so please check the code and provide me the best solution for good performance for fetching the records from above SQL.

Thanks
Kiran
Jan 29 '10 #1
Share this Question
Share on Google+
7 Replies

P: 13
SELECT A.ID,
A.IDTYPE,
A.USERNAME,
A.FIRSTNAME,
A.LASTNAME,
A.STATUS,
A.LANGUAGEID,
A.LANGUAGENAME,
GETROLESFORUSER(A.USERNAME) ROLES,
0 CLUBID,
A.ISDISABLED,
0 REGION
FROM (SELECT W.ID,
W.IDTYPE,
W.USERNAME,
P.FIRSTNAME,
P.LASTNAME,
W.ACCT_STATCD STATUS,
W.PREFERREDLANGUAGE LANGUAGEID,
LT.LANGNAME LANGUAGENAME,
P.SORTFIRSTNAME,
P.SORTLASTNAME,
W.ISDISABLED,
TRUNC(W.CREATEDT) CREATEDT,
REGISTRATION_TYPE
FROM WEBUSER W
JOIN PERSON P ON W.ID = P.ID
AND W.IDTYPE = P.IDTYPE
LEFT JOIN LANGTYPE LT ON W.PREFERREDLANGUAGE = LT.LANGTY) A
WHERE 1 = 1
AND TRUNC(A.CREATEDT) BETWEEN '01-JUN-2009' AND '30-Oct-2009';

Please note below function "getrolesforuser" is being called from above SQL.

FUNCTION getrolesforuser (pusername IN webuser.username%TYPE)
RETURN VARCHAR2
IS
vroles VARCHAR (1000);

CURSOR cursorout (pusernames VARCHAR2)
IS
SELECT /*+INDEX(PH) INDEX(WU)INDEX(PN)*/
NVL2 (c.commdesc,
pn.posdesc || '-' || c.commdesc,
pn.posdesc
) rolename
FROM positionholder ph,
webuser wu,
positionname pn,
(SELECT /*+INDEX(CD)INDEX(CN)*/
cd.commid, cn.commdesc
FROM committee cd, commname cn
WHERE cn.commid = cd.commid
AND cd.effdt <= SYSDATE
AND (cd.enddt >= SYSDATE OR cd.enddt IS NULL)
AND cn.langty = 0) c
WHERE ph.ID = wu.ID
AND ph.idtype = wu.idtype
AND ph.positionid = pn.positionid
AND ph.commid = c.commid(+)
AND ph.effdt <= SYSDATE
AND (ph.enddt >= SYSDATE OR ph.enddt IS NULL)
AND UPPER (wu.username) = UPPER (pusernames)
AND pn.langty = 0
AND pn.gender = 'M'
UNION
SELECT /*+INDEX(PN)*/
pn.posdesc
FROM positionname pn
WHERE pn.posdesc = 'All Constituents';
-- Declare program variables as shown above
BEGIN
FOR rec_cur IN cursorout (pusername)
LOOP
IF vroles IS NULL
THEN
LOOP
vroles := rec_cur.rolename;
END LOOP;
ELSE
vroles := vroles || ', ' || rec_cur.rolename;
END IF;
END LOOP;

RETURN vroles;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put ('error');
END;
END getrolesforuser;

I want select query posted above should fetch the records within half minute.Due to this function above SQL took long tome time for fetching the records so my application gets time out error.The aboue SQL was fetching only 5254 recodes for that date criteria.
So Pls provide some solution on this asap.
Feb 2 '10 #2

P: 13
Quote:
SELECT A.ID,
A.IDTYPE,
A.USERNAME,
A.FIRSTNAME,
A.LASTNAME,
A.STATUS,
A.LANGUAGEID,
A.LANGUAGENAME,
GETROLESFORUSER(A.USERNAME) ROLES,
0 CLUBID,
A.ISDISABLED,
0 REGION
FROM (SELECT W.ID,
W.IDTYPE,
W.USERNAME,
P.FIRSTNAME,
P.LASTNAME,
W.ACCT_STATCD STATUS,
W.PREFERREDLANGUAGE LANGUAGEID,
LT.LANGNAME LANGUAGENAME,
P.SORTFIRSTNAME,
P.SORTLASTNAME,
W.ISDISABLED,
TRUNC(W.CREATEDT) CREATEDT,
REGISTRATION_TYPE
FROM WEBUSER W
JOIN PERSON P ON W.ID = P.ID
AND W.IDTYPE = P.IDTYPE
LEFT JOIN LANGTYPE LT ON W.PREFERREDLANGUAGE = LT.LANGTY) A
WHERE 1 = 1
AND TRUNC(A.CREATEDT) BETWEEN '01-JUN-2009' AND '30-Oct-2009';

Please note below function "getrolesforuser" is being called from above SQL.


Quote:
FUNCTION getrolesforuser (pusername IN webuser.username%TYPE)
RETURN VARCHAR2
IS
vroles VARCHAR (1000);

CURSOR cursorout (pusernames VARCHAR2)
IS
SELECT /*+INDEX(PH) INDEX(WU)INDEX(PN)*/
NVL2 (c.commdesc,
pn.posdesc || '-' || c.commdesc,
pn.posdesc
) rolename
FROM positionholder ph,
webuser wu,
positionname pn,
(SELECT /*+INDEX(CD)INDEX(CN)*/
cd.commid, cn.commdesc
FROM committee cd, commname cn
WHERE cn.commid = cd.commid
AND cd.effdt <= SYSDATE
AND (cd.enddt >= SYSDATE OR cd.enddt IS NULL)
AND cn.langty = 0) c
WHERE ph.ID = wu.ID
AND ph.idtype = wu.idtype
AND ph.positionid = pn.positionid
AND ph.commid = c.commid(+)
AND ph.effdt <= SYSDATE
AND (ph.enddt >= SYSDATE OR ph.enddt IS NULL)
AND UPPER (wu.username) = UPPER (pusernames)
AND pn.langty = 0
AND pn.gender = 'M'
UNION
SELECT /*+INDEX(PN)*/
pn.posdesc
FROM positionname pn
WHERE pn.posdesc = 'All Constituents';
-- Declare program variables as shown above
BEGIN
FOR rec_cur IN cursorout (pusername)
LOOP
IF vroles IS NULL
THEN
LOOP
vroles := rec_cur.rolename;
END LOOP;
ELSE
vroles := vroles || ', ' || rec_cur.rolename;
END IF;
END LOOP;

RETURN vroles;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put ('error');
END;
END getrolesforuser;

I want select query posted above should fetch the records within half minute.Due to this function above SQL took long tome time for fetching the records so my application gets time out error.The aboue SQL was fetching only 5254 recodes for that date criteria.
So Pls provide some solution on this asap.
Feb 2 '10 #3

RedSon
Expert 5K+
P: 5,000
Do not double post. You have been warned.
Feb 2 '10 #4

debasisdas
Expert 5K+
P: 8,127
The simplest reason for the slow performance is you are using a lot of string comparison and concatenation. Check if you can avoid those.
Feb 8 '10 #5

P: 13
Hi,

These are the mendatoday things STRINGS and CONCAT operators.


Please suggest me if you have any new soltion for fetching the records fast.


Best Regards,
Kiran Rajenimbalkar
Feb 8 '10 #6

amitpatel66
Expert 100+
P: 2,367
Generate Explain Plan / Trace on the Query and check what could be the problem
Feb 10 '10 #7

amitpatel66
Expert 100+
P: 2,367
Duplicate Threads merged for better management of the forum

Moderator
Feb 10 '10 #8

Post your reply

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