SQL Not working with the function

[kiranrajenimbalkar]

Hi,

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

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';

In this SQL one function is being used GETROLESFORUSER and also i am executing above SQL I got the error function return without value..

CREATE OR REPLACE FUNCTION GETROLESFORUSER(PUSERNAME IN WEBUSER.USERNAME%TYPE)
 
 RETURN VARCHAR2 IS
 
  VROLES VARCHAR2(32767);
 
  TYPE MY_ROLES IS TABLE OF VARCHAR2(100);
 
  MYROLE MY_ROLES;
 
  CURSOR CURSOROUT(PUSERNAME VARCHAR2) IS
    SELECT NVL2(C.COMMDESC, PN.POSDESC || '-' || C.COMMDESC, PN.POSDESC) ROLENAME
      FROM POSITIONHOLDER PH,
           WEBUSER WU,
           POSITIONNAME PN,
           (SELECT 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(PUSERNAME)
       AND PN.LANGTY = 0
       AND PN.GENDER = 'M'
    UNION
    SELECT PN.POSDESC
      FROM POSITIONNAME PN
     WHERE PN.POSDESC = 'All Constituents';
 
BEGIN
 
  OPEN CURSOROUT(PUSERNAME);
 
  FETCH CURSOROUT BULK COLLECT INTO MYROLE;
 
  FOR REC_CUR IN MYROLE.FIRST .. MYROLE.LAST LOOP
 
    IF MYROLE(REC_CUR) IS NOT NULL THEN
 
      VROLES := VROLES || ', ' || MYROLE(REC_CUR);
 
    END IF;
 
  END LOOP;
 
  VROLES := SUBSTR(VROLES, 3);
 
  RETURN VROLES;
 
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      DBMS_OUTPUT.PUT('error');
    END;
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

[kiranrajenimbalkar]

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.

[kiranrajenimbalkar]

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.

[RedSon]

Do not double post. You have been warned.

[debasisdas]

The simplest reason for the slow performance is you are using a lot of string comparison and concatenation. Check if you can avoid those.

[kiranrajenimbalkar]

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

[amitpatel66]

Generate Explain Plan / Trace on the Query and check what could be the problem

[amitpatel66]

Duplicate Threads merged for better management of the forum

Moderator