A Table Join Performance Issue in RTC Report
I'd like to implement a report to weekly WI state information of a Quey Result.
So I joined 2 table: WORKITEM_QUERY_RESULTS and WORKITEM_STATES.
But it is very slow because there are too many many records in WORKITEM_STATES. I have minimize the result size of WORKITEM_STATES by limiting project area and start/end date.
But the it is still very slow and report a error.
Is there any solution to resolve this problem?
Thank you very much!
So I joined 2 table: WORKITEM_QUERY_RESULTS and WORKITEM_STATES.
But it is very slow because there are too many many records in WORKITEM_STATES. I have minimize the result size of WORKITEM_STATES by limiting project area and start/end date.
But the it is still very slow and report a error.
Is there any solution to resolve this problem?
Thank you very much!
One answer
The WORKITEM_STATES table can have millions of rows. Birt joins do not result in SQL joins. BIRT will fetch all the contents of both joined data sets. This explains the slow performance.
Unfortunately, I do not see an efficient way to do the above.
To do this, we will need some back-end changes to expose the Workitem ID in the WORKITEM_STATES tables so that the resulting Workitem IDs from the Work Item Query can be passed as parameter values to the WORKITEM_STATES table. Even that could be slow as we could end up with a big IN clause. The issue here is that you are trying to join two data sets which have two different query models that cannot be combined.
Unfortunately, I do not see an efficient way to do the above.
To do this, we will need some back-end changes to expose the Workitem ID in the WORKITEM_STATES tables so that the resulting Workitem IDs from the Work Item Query can be passed as parameter values to the WORKITEM_STATES table. Even that could be slow as we could end up with a big IN clause. The issue here is that you are trying to join two data sets which have two different query models that cannot be combined.