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
7 2483
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.PREFERREDLANG UAGE LANGUAGEID,
LT.LANGNAME LANGUAGENAME,
P.SORTFIRSTNAME ,
P.SORTLASTNAME,
W.ISDISABLED,
TRUNC(W.CREATED T) CREATEDT,
REGISTRATION_TY PE
FROM WEBUSER W
JOIN PERSON P ON W.ID = P.ID
AND W.IDTYPE = P.IDTYPE
LEFT JOIN LANGTYPE LT ON W.PREFERREDLANG UAGE = LT.LANGTY) A
WHERE 1 = 1
AND TRUNC(A.CREATED T) BETWEEN '01-JUN-2009' AND '30-Oct-2009';
Please note below function "getrolesforuse r" is being called from above SQL.
FUNCTION getrolesforuser (pusername IN webuser.usernam e%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)INDE X(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.rolenam e;
END LOOP;
ELSE
vroles := vroles || ', ' || rec_cur.rolenam e;
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.
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.PREFERREDLANG UAGE LANGUAGEID,
LT.LANGNAME LANGUAGENAME,
P.SORTFIRSTNAME ,
P.SORTLASTNAME,
W.ISDISABLED,
TRUNC(W.CREATED T) CREATEDT,
REGISTRATION_TY PE
FROM WEBUSER W
JOIN PERSON P ON W.ID = P.ID
AND W.IDTYPE = P.IDTYPE
LEFT JOIN LANGTYPE LT ON W.PREFERREDLANG UAGE = LT.LANGTY) A
WHERE 1 = 1
AND TRUNC(A.CREATED T) BETWEEN '01-JUN-2009' AND '30-Oct-2009';
Please note below function "getrolesforuse r" is being called from above SQL.
Quote:
FUNCTION getrolesforuser (pusername IN webuser.usernam e%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)INDE X(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.rolenam e;
END LOOP;
ELSE
vroles := vroles || ', ' || rec_cur.rolenam e;
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 5,000
Recognized Expert Expert
Do not double post. You have been warned.
The simplest reason for the slow performance is you are using a lot of string comparison and concatenation. Check if you can avoid those.
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
Generate Explain Plan / Trace on the Query and check what could be the problem
Duplicate Threads merged for better management of the forum
Moderator
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: lawrence |
last post by:
I'm a beginner with Javascript and especially cross-browser
Javascript. I got this working in IE, but not in Netscape 7. It seems
like, in Netscape, every time I click on a button, the focus shifts to
that button, so there is no text to be selected. What should I do?
Below you'll see some code that I have in one of my forms. I was
hoping to have these buttons and when I click on them they would take
selected text from a textarea box and...
|
by: AussieRules |
last post by:
Hi,
I want to use the user color scheme to set the color of my forms.
I now I have to use the. System.Drawing.SystemColors, but which color is the
color of a form background as used in other applications.
In the end all I want to do is
form1.backcolor = system.whatever.color
|
by: serkan |
last post by:
Guys,
I am trying to get this password reset functionality wor for me but I am not successful at all. Please somebody help me. I get "Your password could not be reset - please try again later" so I think the get_random_word function is not working right. Here are the scripts:
This is the script after the user enters his/her userid.
<?php
require_once("bookmark_fns.php");
// creating short variable name
$username = $_POST;
|
by: Q1tum |
last post by:
Hi all, I have a problem with my script, I'm using javascript DOM to
update my page.
The getPortals() function updates a select list whichs shows portals.
The doUpdate() function updates a portal and after that it re-builds
the select list by calling the getPortals function at the end of the
update function.
This is working in Opera (without the alert() at the doUpdate()
|
by: Nx |
last post by:
i've got it all working nicely in firefox, but whenever i test it in IE
none of the onclick events are triggered.
i'm using an xsl to transform an rss feed into a photogallery.
when i try to use setAttribute FF and safari work, but IE stops working
when i used addEventListener and attachEvent safari stops working
and when i tried .onClick,none of them worked
being fairly inexperienced (but learning fast), i figure i'm doing it
| |
by: shyamg |
last post by:
hi all,
This javascript is working IE but not working in FIreFox, validating text fields.
var dealerid = new keybEdit('abcdefghijklmnopqurstuvwxyz01234567890 ','Alpha-numeric input only.');
var dealinit = new keybEdit('abcdefghijklmnopqurstuvwxyz01234567890 ','Alpha-numeric input only.');
var dealername = new keybEdit('abcdefghijklmnopqurstuvwxyz ','Alphabets input only.');
var rank = new...
|
by: ajos |
last post by:
hi frnds,
i have a form,which has 2 input text boxes, the values are entering the text boxes,when i leave the 2 text boxes blank and hit submit a java script gives the message that the 2 fields are blank.....the problem now is when i leave the text boxes blank the message appears...and when i click ok...then instead of showing me the page its giving me an exception-->
type Exception report
message
description The server encountered an...
|
by: BibhuAshish |
last post by:
Hello guys
i have one function which converts the ip/netmask into networkid/netmask and also it shows the message to user that his ip is changed to network id. but that function is working in mozilla perfectly but not in IE.
To check where is the fault i have written so many alert() but sometimes these are working.
Please someone read my code and tell me why its not working.
i am also sending you the function validateIPandNetMask()
my...
|
by: Ja NE |
last post by:
(as first - thank you for all your help several years ago, and second - please
apologise me for my clumsy enlgish)
so, preface: some users on my photo related site sometimes enter (link) giant
photo in their post or coment, and while I was writting new bbcode for my
forum, I have got idea to check size of linked pictures to scale them down if
needed (matamethic of that part isn't written in folowing code and would not
be a problem).
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |