Why pulling a field from one table ... causes performance drop

[mirogak]

Hi guys,

I have a report that pulls from

- a SALES table (very very big)
- a PRODUCT table (reasonably big)
- a BUDGET table (very small)

I have these three tables joined. When I pull fields from the SALES and the PRODUCT table, everything is GREAT.

I get a YTD summary view of sales GROUPED by month.

BUT as soon as I pull a field from the BUDGET table, which is a very small table, the PERFORMANCE goes REALLY REALLY down the DRAIN. I join the MONTH column of my BUDGET table with the MONTH column of my SALES table. I know, it doesn't make sense to drag the Budget column into the details section of the report, but rather to the GROUP FOOTER, because the BUDGET table has monthly data rather daily. But no matter where I pull the field to (DETAIL section or GROUP footer section), performance takes a huge hit.

Any idea, whats going on And I know I got my joins correct, cuz I am getting the right result.

Background: The budget table basically has sales targets for every division that they gotta hit. And thats why i compare my actual month sales with what the budget target was.

thanks,

 

[Turkbear]

Hi,
More info please:

What Database and Version?
What Connection Method?

Are the linked fields indexed?

Which MONTH field are you grouping on ( sales,budget..)?






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

 

[mirogak]

Here is more info:

Using
-Crystal Report XI (11.5)
-Oracle 8i database

Joining within CR
- SALES.item joined with PRODUCT.item
- SALES.month joined with BUDGET.month
- SALES.division joined with BUDGET.division

GROUP on
- SALES.month

SUMMARY for every GROUP on
- SALES.gross_sales
- SALES.gross_returns
- SALES.net_sales

The BUDGET tables has targets amounts for every division for every month of the year.

I just realized that I don't have any indexes on the BUDGET table. But do I really need one; it only has 36 rows, since we have 3 divisions times 12 months = 36 rows of data.

Thanks,

 

[Turkbear]

Hi,
Since it is Oracle, try this..
Use the Show Sql menu tab and copy the Sql generated by Crystal...
Using SqlPLus ( or some other tool) run that query directly in Oracle ( preferably using an Explain Plan ) to see where the bottleneck is..





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

 

[mirogak]

I should close this one.

Turkbear, thanks for your input. You would have probably nailed this issue if I hadn't left out the following:

The month column in BUDGET table that I was using to join with, was in fact being modified using the "substr" function through the Add Command in CR.

Clarification: the format of the month column was a little different than how it appeared in the other joined table, SALES. So I use the Add Command to generate my own SQL function to join the two tables. And I was a using the "substr" function on the month field. I think that's why it was taking forever. My SALES table was huuuuuge and every time it was join records it was calling that "substr" function.

Resolution: I added another month column in the BUDGET table whose format was exactly the same as in the SALES table, and I then used standard CR joins. It was lightining fast ... almost... lol

Thanks
Mirogak

 

[Turkbear]

Hi,
Glad you got it working..
You could also have added a Function Based Index on on the BUDGET Table for the Substr of MONTH you were using.





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