i have a crystal report, oracle is pumping it out a million rows of data. regularly i would expect this report to have 50k-5mil rows of data sent to it from oracle. the 1 million rows oracle starts returning in sql*plus within half an hour from my stored procedure. i wonder if turning the stored proc into a pipelined one would help; answer i'm getting now is not really.
we have an online view of the report and an export to excel view. the online report is through the web with the crystal viewer. last run this report took 12 hours to complete. not sure how large it is (MB) - there are basically 3 types of pages created off the data; i believe these are called subreports. i am not a crystal developer, i did developer the proc that returns the million rows though.
the way the report works, is you see a list of top level entities (repeated multiple times in the data) - clicking on one of them takes you to a 2nd level entity (repeated) and clicking on one of these takes you to a 3rd page where all the (unique) products reside in that entity.
current dataset:
A z 1234
A z 2222
A y 3333
B m 5555
cr page 1:
A
B
user clicks A; page 2:
z
y
user clicks z; page 3:
1234
2222
of the 12 hours (only ran online/crystal viewer version) i expect the data to be returned in... 1 hour? not sure. majority of the time appears to be on crystal's part for processing though. does crystal start processing as soon as it tastes data, or does it need to wait for the whole result set? can i break up the pages into different procs (3) and still maintain links between them? please help, interesting in any and all suggestions. thanks!
Start Free Trial
