Group tuning problems

[pandpp]

Hello again.

CR10
Oracle 8i

I have a report, which runs off a VIEW. I have tuned the view, such that I am being returned around 54,000 records in around 8 seconds, off a large number of tables.

The report that this feeds has 4 levels of grouping in it. As such, there are effectively 4 levels of sorting that needs to be done on the data.

At this point, I have a number of options:
1) Let CR ask Oracle to do the sorting, which works, except, takes 46 MINUTES to sort the first level. This is via an ORDER BY in the generated SQL.
2) Let CR itself do the sorting as it reads the records in.

If I take option (1), then, once the records are sorted, they are read in VERY quickly, maybe 1000+/second. This is presumably because CR trusts the order in which the records are arriving.
On the other hand, using option (2), the records come in VERY slowly, 50/second. I also presume this is because CR doesn't have the best sorting routine, which is understandable.

There is a third option, seeing as how I only have 2 values in the first group. When I extract the records, I can select the first value seperately & UNION ALL that with the records of the second value. This then provides me with my sorted list, in just 8 seconds again.
The issue here though is that CR does NOT believe they are sorted, even when I tell the Group to use 'Original Order', so, it reads the records in VERY slowly again.

Does any one know a way to make CR read the records in quickly AND NOT have it try to sort the records?

I previously experienced similar performance issues with an Oracle Stored Procedure, which I put down to the way CR had to tease the records out, 1 at a time, via a CURSOR. I now find myself doubting this previous assumption.


At this point, ANY idea will be attempted, seeing as how I am running out of ideas to follow myself.

Thanks,

Peter.

 

[IdoMillet]

Peter,

Did you use a VIEW or a COMMAND with the UNION ALL approach?

If a VIEW, than I don't see how Crystal can tell the difference between that View and the VIEW in your Option #1 above.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pandpp]

Ido,

I had only tried the UNION ALL in a VIEW, until this morning. I have since tried it in a COMMAND, as a result of your question.

The times I am experiencing are as below:
COMMAND including UNION ALL, Group options as Original Order
Query = 1211 ms
Read = 34166 ms
Records = 540 (extra filtering not applied for testing purposes)

Oracle VIEW, Group options as Ascending Order, causing CR to do ORDER BY
Query = 21670 ms
Read = 386 ms
Records = 532

Oracle VIEW including UNION ALL, Group options as Original Order
Query = 1389 ms
Read = 23124 ms
Records = 532

As can be seen from this, my best option is still to get Oracle to do the sorting, via CR, only, 22 seconds to return a mere 532 records is excessive.

I'm still VERY keen for new ideas.

Thanks,

Peter.

 

[IdoMillet]

Simply use the UNION VIEW and don't use the "Original Order" option in Crystal.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pandpp]

Ido,

Thanks for the suggestion. I tried that one previously, with less than impressive results.

If I leave the grouping option as Ascending Order, that causes an ORDER BY clause to be issued to Oracle, times as follows:
Query = 19956 ms
Read = 384 ms

I even tried setting the group to Specified Order, which resulted in:
Query = 1463 ms
Read = 24697 ms

I have a number of these reports, all subtlely different. I noticed yesterday with one of them, that, it reads the records quickly, doesn't issue an ORDER BY to Oracle & still has a number of levels of grouping.
I am following up on this now, to see what the difference is.

Once I have some more detailed research result, I'll post back.

Thanks for the ideas so far.

Peter.

 

[IdoMillet]

Make sure the report has no Record Sort, just Grouping.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

54k rows that take 46 minutes to sort???

Fire the dba, or perhaps you haven't spoken to her/him?

Depending upon the View design, the optimizer may be getting tripped up, but that kind of performance is insane unless this is an extremely complicated database, or poorly tuned. Someone well versed needs to look at the execution plan.

I would also suggest taking these questions to an Oracle forum (at least copy them there) as many here are not experts in database design.

-k