External join performance issue at report level

[former_member290153]

Hi All,

SAP crystal report version :2008.

Issue Description: External join performance issue at report level when we have 2 different database tables join on 2 columns

Impact: Query took around 4 minutes but it should be 30 sec as prod existing report

No of users impacted: 350

Thanks in advance

Priyanka.

[former_member11696]

Removed your e-mail address, not allowed.

CR does not support multiple data sources in main or subreport, that is what subreports are for, single data source only.

Filtering will be client side in this case.

I suggest you create a Stored Procedure and link Server side if that's possible.

Don

[Former Member]

Hi Abhilash,

Thanks for Reply.

Let me tell you briefly about the issue.

I have 3 command queries one is from db2 database and 2 are from sql dabase

db2 table has join with sql table1 and the same db2 table has join with other sql table2 so I could see two external joins in show sql query

but as per my requirement I have added rno column in sql table2 and tried to add that query in add command but it won't allowing me and after I paste my query with out having join conditions in add command and given links to db2 table and sql table2 in report. Report is working as expected. But seems there is one additional external join has created in my modified report but where as that external join was not there in my actual query as we WHERE clause in existing prod report.

By that additional external join, report took around 4 min of time but it should be 30 sec.

existing sql table2 command query:

SELECT * from table2
WHERE Table2.ID={?DSNOGW01: db2.PROCESS_ID} AND "Table2"."Unit_ID"={?DSNOGW01: db2.UNIT_NO}

Modified query:

SELECT * from (select Table2.*,row_number() over (partition by column1,coulmn2,column3,column4,column5 order by ProcessUnit_ID asc) rno from Table2) E WHERE E.rno=1
EXTERNAL JOIN Table2.ID={?DSNOGW01: db2.PROCESS_ID} AND "Table2"."Unit_ID"={?DSNOGW01: db2.UNIT_NO}

I want to be put where clause with my changes in modified query but I can't.

I Thank full to Priyanka and Abhilash for moving this issue forward.

[abhilash_kumar]

Hi, Priyanka

The decrease in performance when you join two disparate sources, is very much expected.

CR has to first read all data from the first source, then the next source before joining them together - this process slows down the report's performance.

If you wish to run this report any faster, you should consolidate the two sources in a single database using an ETL solution.

-Abhilash