Performance Issue on a Report with Subreport

[elinsd]

I have a report that has a one-to-many relationship between the main report and the subreport. Since it is pulling a full month long data and the subreport's main table is left-outer-joined with three other tables so it takes an hour to run. I have isolated the problem being on the report side because the same SQL query runs fast (less than a minute) against the database. The main report runs very fast without the subreport so I'm really inclined to see any workaround or improvement to the subreport.

Can some one advise me a good way to solve this problem? I read somewhere that you can use array to eliminate the need of the subreport so the report can run fast but I don't know how to write the codes to do that. I'd appreciate if someone can show me some sample codes.

 

[Turkbear]

Hi,
By the 'same SQL Query' do you mean one that has all the left-outer joins and all the tables used by the main and subreports?
If so, why not use that query as a command object and base the report on that ?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

If you post technical information you'll get technical answers.

State where the subreport is in the main report, what (if?) it is linked to the main report, and how you tested the same query.

A subreport in a group or the details section will execute for every occcurenc of the respective group or details.

Not posting the Crystal version, the database/connectivity used is generally idictive of frustration, relax, we'll get it straightened out, but you need to post meaningful information and minimize your descriptions of data and problems.

In general, post:

Crystal version
Database/connectivity
Example data
Expected output

And avoid subreports unless they are in a report footer or some such (meaning that at least the SQL will only fire once per report).

-k

 

[elinsd]

Thanks, guys. I didn't want to got rid of the subreport in the beginning but that was the only option I had so I did. Now the report runs fine without the overhead of running the subreport for each record on the main report.

 

[synapsevampire]

If the subreport was in the beginning, than it was not running for every record.

As previously posted, you share nothing technical in your post, and your solution seems strange if it includes data that you need...

If you decide that you might want to learn how to resolve this, post meaningful environment and requirements.

-k