| SQl Query Performance in BIRT [message #630890] |
Tue, 05 October 2010 13:25  |
 Gaurav Sahni
Messages: 19
Registered: September 2010 |
Junior Member |
|
|
Hi
I have one sql which is taking only 5 second when we run through pl/sql developer but when we run the report using birt is taking 50 second
Here is the sql
SELECT distinct MAINTAINING_BUSINESS_UNIT,count (distinct APP_ID) from
(SELECT DISTINCT T410.APP_ID,
T6897.PUBLICATION_DATE,
T1135.CAL_DIM_YEAR_NUM,
T410.MAINTAINING_BUSINESS_UNIT,
T410.MAINTAINING_DIVISION,
T410.APP_NAME,
T623.TECHNOLOGY_NAME,
T524.INVESTMENT_POSTURE_CODE,
T441.APPL_COMPLIANCE_RANKING_DESCR
FROM DIM_APPLICATION T410,
DIM_INVESTMENT_POSTURE T524,
DIM_TECHNOLOGY T623,
FACT_TECHNOLOGY_ROADMAP T12824,
IA_DATES T1135,
DIM_APPL_COMPLIANCE_RANKING T441,
FACT_APO_STAT T635,
FACT_APO_DTL_CI_RANKING T6897
WHERE (T410.APP_KEY = T635.APP_KEY
AND T410.APP_KEY = T6897.APP_KEY
AND T410.INVESTMENT_POSTURE_KEY = T524.INVESTMENT_POSTURE_KEY
AND T623.TECHNOLOGY_KEY = T12824.TECHNOLOGY_KEY
AND T635.DATE_KEY = T1135.DATE_KEY
AND T6897.TECHNOLOGY_PRODUCT_VERSION_KEY =
T12824.TECHNOLOGY_PRODUCT_VERSION_KEY
AND T6897.PUBLICATION_DATE = trunc(sysdate - 1)
AND T6897.RANKING_VALID_END_DATE >= trunc(sysdate - 1)
--AND T441.APPL_COMPLIANCE_RANKING_KEY = T635.APPL_COMPLIANCE_RANKING_KEY
AND T441.APPL_COMPLIANCE_RANKING_KEY =
T6897.APPL_COMPLIANCE_RANKING_KEY
AND T6897.RANKING_VALID_START_DATE <= trunc(sysdate - 1)
AND to_date(T635.date_key, 'j') = trunc(sysdate - 1))
)
GROUP BY MAINTAINING_BUSINESS_UNIT
could you please let me know why it is taking so much time while runingthrough birt
|
|
|
| Re: SQl Query Performance in BIRT [message #630944 is a reply to message #630890] |
Tue, 05 October 2010 15:46  |
|
Gaurav,
How are you running it in BIRT (using the preview dataset or report)?
Can you open a bugzilla entry to track this. We always like to track
performance issues.
Thanks
Jason
On 10/5/2010 9:25 AM, Gaurav Sahni wrote:
> Hi I have one sql which is taking only 5 second when we run through
> pl/sql developer but when we run the report using birt is taking 50 second
> Here is the sql
> SELECT distinct MAINTAINING_BUSINESS_UNIT,count (distinct APP_ID) from
> (SELECT DISTINCT T410.APP_ID,
> T6897.PUBLICATION_DATE,
> T1135.CAL_DIM_YEAR_NUM,
> T410.MAINTAINING_BUSINESS_UNIT,
> T410.MAINTAINING_DIVISION,
> T410.APP_NAME,
> T623.TECHNOLOGY_NAME,
> T524.INVESTMENT_POSTURE_CODE,
> T441.APPL_COMPLIANCE_RANKING_DESCR
> FROM DIM_APPLICATION T410,
> DIM_INVESTMENT_POSTURE T524,
> DIM_TECHNOLOGY T623,
> FACT_TECHNOLOGY_ROADMAP T12824,
> IA_DATES T1135,
> DIM_APPL_COMPLIANCE_RANKING T441,
> FACT_APO_STAT T635,
> FACT_APO_DTL_CI_RANKING T6897
> WHERE (T410.APP_KEY = T635.APP_KEY
> AND T410.APP_KEY = T6897.APP_KEY
> AND T410.INVESTMENT_POSTURE_KEY = T524.INVESTMENT_POSTURE_KEY
> AND T623.TECHNOLOGY_KEY = T12824.TECHNOLOGY_KEY
> AND T635.DATE_KEY = T1135.DATE_KEY
> AND T6897.TECHNOLOGY_PRODUCT_VERSION_KEY =
> T12824.TECHNOLOGY_PRODUCT_VERSION_KEY
> AND T6897.PUBLICATION_DATE = trunc(sysdate - 1)
> AND T6897.RANKING_VALID_END_DATE >= trunc(sysdate - 1)
> --AND T441.APPL_COMPLIANCE_RANKING_KEY = T635.APPL_COMPLIANCE_RANKING_KEY
> AND T441.APPL_COMPLIANCE_RANKING_KEY =
> T6897.APPL_COMPLIANCE_RANKING_KEY
> AND T6897.RANKING_VALID_START_DATE <= trunc(sysdate - 1)
> AND to_date(T635.date_key, 'j') = trunc(sysdate - 1))
> )
> GROUP BY MAINTAINING_BUSINESS_UNIT
>
> could you please let me know why it is taking so much time while
> runingthrough birt
>
>
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.02852 seconds
] 
Search
Help
Register
Login
Home
