Hi,
I have a problem in my below SQL,it took much time for getting the result(execution time is around 10 minutes).This sql contains many functions and also a DISTINCT keyword and its mandatory.So request you to please provide me solution for this. Indexes are exits and also I have analyzed all tables which been used inside this SQL.
I have removed the DISTINCT keyword its gave me result but its return duplicate records so i need it so please provide me the solution ASAP. - SELECT DISTINCT PC.ID,
-
PC.IDTYPE,
-
PC.CONSTROLEID,
-
PC.CONSTROLEPRIMARYYN,
-
CATMS.MAINCATID,
-
CATMS.SUBCATID,
-
MCAT.MAINCATDESC,
-
SCAT.SUBCATDESC,
-
B.BENID,
-
B.BENDESC BENEFICIARY,
-
P.PROJECTID,
-
P.PROGRAMID,
-
SUBSTR(DECODE(P.OPENCLOSEIND, 'C', 'C', 'O', 'O', NULL, 'P'),
-
1,
-
1) OPENCLOSEIND,
-
DECODE(P.OPENCLOSEIND,
-
NULL,
-
'In Process',
-
'C',
-
'Closed',
-
'O',
-
'Open') STATUS_TYPE,
-
PRJ_GETPROJECTSTATUS_STATUSID(P.PROJECTID) CURRENTSTATUSID,
-
SL.STATUSID,
-
SL.STSDESC STATUSDESC,
-
TRUNC(PS.EFFDT) EFFDT,
-
PS.ENDDT,
-
SUBSTR((PRT.PROGTYSDESC || DECODE(P.FINLOCKYN,
-
'Y',
-
SUBSTR(P.APPRYEAR, 3, 2),
-
'__') || P.PROJECTID),
-
1,
-
100) PROJECT_ID,
-
PRT.PROGTYSDESC PROGTYSDESC,
-
PRT.PROGTYDESC GRANTTYPE,
-
P.PROJDESC PROJDESC,
-
RI.NAME TRFSTAFF,
-
PRJ_GETCOUNTRYNAME(P.LOCCNTRYID) PROJECTCOUNTRY,
-
-
SUBSTR(PRJ_GETPROJECTSTATUS(P.PROJECTID), 1, 100) CURRENTSTATUS,
-
GETLASTPAYMENTDT(P.PROJECTID) LASTPAYMENT_DATE,
-
GETREPORTEDDT(P.PROJECTID) REPORTEDDATE,
-
PRJ_GETPROJECTCURRSTATUSDATE(P.PROJECTID) STATUSEFFDATE,
-
PS.ENDDT STATUSENDDATE,
-
P.APPRDT APPROVDATE,
-
P.EFFDT PROJECTSTARTDATE,
-
P.ENDDT PROJECTENDDATE,
-
DECODE(P.FINLOCKYN, 'Y', PRJ_GETAWARDAMOUNT(P.PROJECTID), 0) TRFAWARD,
-
SUBSTR(PROJECTINVOLVEMENT(PC.CONSTROLEPRIMARYYN,
-
PC.FUNDINGYN),
-
1,
-
200) INVOLVEMENT,
-
CR.CONSTROLEDESC CONST_ROLE,
-
CT.CONSTTYPEDESC CONST_TYPE,
-
PRJ_FORMATNAMEFROMID(PC.ID, PC.IDTYPE, 'N') CONST_NAME,
-
PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT) DISTRICT,
-
PRJ_GETCOUNTRY(PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT),
-
PC.ID,
-
PC.IDTYPE) CONST_COUNTRY,
-
PRJ_GETZONEID(PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT)) ZONEID,
-
PRJ_GETCONSTCASH(PC.PROJECTID,
-
PC.ID,
-
PC.IDTYPE,
-
PC.CONSTROLEID) CASHCOMMITTED,
-
PRJ_GETCONSTDDF(PC.PROJECTID,
-
PC.ID,
-
PC.IDTYPE,
-
PC.CONSTROLEID) DDFCOMMITTED,
-
PRJ_GETTOTALFUNDING(P.PROJECTID) TOTALFUNDING,
-
CT.CONSTTYPEID NO_OF_VOLUNTEER,
-
PRT.PROGTYID NO_TOTALS_IF_IG_3
-
-
FROM PROJECT P,
-
PROJECTCONSTITUENT PC,
-
PROGRAM PR,
-
PROGRAMTYPE PRT,
-
PROJECTSTATUS PS,
-
PROGRAMPHASESTATUS PPS,
-
STATUSLIST SL,
-
CONSTITUENTROLE CR,
-
CONSTITUENTTYPE CT,
-
PROJECTBENEFICIARY PB,
-
BENEFICIARY B,
-
PROJECTCATEGORY PCAT,
-
CATEGORYMAINSUB CATMS,
-
MAINCATEGORY MCAT,
-
SUBCATEGORY SCAT,
-
ADMINUSERDATA RI
-
-
WHERE P.PROJECTID = PC.PROJECTID
-
AND P.PROGRAMID = PR.PROGRAMID
-
AND PR.PROGTYID = PRT.PROGTYID
-
AND P.PROJECTID = PS.PROJECTID
-
AND PS.PROGPHASESTATUSID = PPS.PROGPHASESTATUSID
-
AND PPS.STATUSID = SL.STATUSID
-
AND P.PROJECTID = PB.PROJECTID(+)
-
AND PB.BENID = B.BENID(+)
-
AND P.PROJECTID = PCAT.PROJECTID(+)
-
AND PCAT.CATID = CATMS.CATID(+)
-
AND CATMS.MAINCATID = MCAT.MAINCATID(+)
-
AND CATMS.SUBCATID = SCAT.SUBCATID(+)
-
AND PC.CONSTROLEID = CR.CONSTROLEID
-
AND PC.CONSTTYPEID = CT.CONSTTYPEID
-
AND P.ADMINUSERID = RI.ADMINUSERID(+)
-
AND CATMS.MAINCATID = 5
-
AND CATMS.SUBCATID = 12
-
AND P.OPENCLOSEIND = 'C'
Best Regards,
Kiran
1 1366
Same problem as with your last posting:
If you don't post the execution plan, noone will be able to help you
Sign in to post your reply or Sign up for a free account.
Similar topics
by: rvdw |
last post by:
Hi All,
I've a serious problem with executing stored procedures (SQL2000) from
an Access db (version 97). After executing a stored procedure ,
msaccess hangs. The whole call to the procedure is...
|
by: Nick Coghlan |
last post by:
Anyone playing with the CPython interpreter's new command line switch might have
noticed that it only works with top-level modules (i.e. scripts that are
directly on sys.path). If the script is...
|
by: Nick Coghlan |
last post by:
Python 2.4's -m command line switch only works for modules directly on sys.path.
Trying to use it with modules inside packages will fail with a "Module not
found" error. This PEP aims to fix that...
|
by: Brian Burgess |
last post by:
Hi all,
Anyone know of any special issues with Server.Execute?
Specifically for me it simply is not executing ... the server does not
transfer to the page specified. The calling ASP just...
|
by: Heiko Pliefke |
last post by:
Hi NG!
I wrote a stored procedure which at first deletes 100.000 rows and then
inserts 100.000 new rows.
There is a huge difference between executing this SP in the
query-analyzer (runtime...
|
by: Jibey |
last post by:
Hello:
I'm facing a very strange problem.
When I run my Web application in Visual Studio.NET the Page_Load event is not executing. Other events like a Button_Click are executing.
It doesn't...
|
by: Edward Yang |
last post by:
In my test, I have two web applications app1 and app2, and I configure
them as follows in IIS:
$/comboapps
|--web.config
|--bin
| app1.dll
| app2.dll
|--app1
| form1.aspx
|
by: whidbeywave |
last post by:
Hello all,
Help me understanding this situtation. While debugging a scenario for
error 403.9 on IIS/XP Pro, I added two counters to perfmon app.I saw at
some point of time Session Active =0 , but...
|
by: CodeGrommet |
last post by:
I'm testing out the system() command. I coded the following. How can
I output the results into the given output file? Your help will be
appreciated.
/* system example : DIR */
//code based...
|
by: =?Utf-8?B?U2hhbG9t?= |
last post by:
I have installed iis 7 on my win vista home premium Sony Vaio laptop, and
then tried executing downloaded sample working asp scripts in the wwwroot
directory to try out.
The first asp script...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |