Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » SQl Query Performance in BIRT(Sql query Performance)
SQl Query Performance in BIRT [message #630890] Tue, 05 October 2010 13:25 Go to next message
Gaurav Sahni is currently offline 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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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
>
>
Re: SQl Query Performance in BIRT [message #631061 is a reply to message #630944] Wed, 06 October 2010 03:24 Go to previous messageGo to next message
Gaurav Sahni is currently offline Gaurav Sahni
Messages: 19
Registered: September 2010
Junior Member
It is taking time for both preview data set and also while runing report throurh birt viewer
Re: SQl Query Performance in BIRT [message #631196 is a reply to message #631061] Wed, 06 October 2010 14:25 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Did you open a bug for this?

Jason

On 10/5/2010 11:24 PM, Gaurav Sahni wrote:
> It is taking time for both preview data set and also while runing
> report throurh birt viewer
Previous Topic:split() in a computed column preview different from displayed in report
Next Topic:Cannot set Shared Resources folder
Goto Forum:
  


Current Time: Mon Oct 20 08:26:28 GMT 2014

Powered by FUDForum. Page generated in 0.01940 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software