Very Slow Sub summary Performance

[Goetch]

I created a sub summary report that also uses filtered portals to create a report for our companies sales.  I thought it would be neat if I could show the monthly sales by department for each year we have on record.  My goal was to show each department as a row and each month's sales as columns.  The report works, and shows the correct data, however it takes hours to run and summarize the data making the report unusable as it stands.  I even tested just using one department and one year to see how long it would take to generate the report, and it took about 30 minutes before it was finished. 

 

In a nut shell this is what I did. For the basis of this report I have a "Sales" table that contains the following fields:  "Date of Sale" "price" "quantity" "Extended Price"(calculation that takes qty*price), "department" , "month of sale" (calc month (date of sale) , "year of sale" (calc year (date of sale) and a summary field "total price" (summary extended price). 

 

 

I started off by creating a Cartesian self join to a TO allsales.  I then created a new layout based on the TO Sales and made a sub summary by year, and a sub summary by department.  I then made 13 portals from the "allsales" TO and filtered them ( Sales Sales::Department = Sales All Sales::Department and Sales Sales::Year of Sale = Sales All Sales::Year of Sale and Sales All Sales::Month of Sale = 1) and so on for each month and then a total for the last filtered portal.  I put the "total price" summary field in the portals. 

 

This enabled the data to be viewed as I would like it and the totals were correct, however it takes what seems like forever for the data to populate.  I was just wondering as to why it takes so long to summarize this data set?  Currently there are about 450,000 records in the database.  But even when I limit the records to one department for one year (about 5000 records) it still takes an incredibly long time to populate the data.  I have been reading through the forums and I guess this is basically a "cross tab" report, so I guess I can try other techniques, but this was so simple compared to what I have read. 

[bruceR]

You've selected the worst possible way to do things.

 

No, you REALLY don't want to use a portal filter on a cartesian relationship to 450,000+ records.

 

Portal filters should generally be used only where the base relation restricts the related record count to the low thousands or less.

 

A portal filter is basically an unstored calculation that is performed, record by, across every related record, every time the portal refreshes.

So you have created a calculation that recalculates 450,000 X 13 times every time you view the page.

 

Why not just use a standard subsummary report?

 

There are other ways to collect this summary data.

[Goetch]

Thank you for the explanation and the tip.  I didn't realize what was happening under the hood, but I probably should have, it makes total sense now.  Thank you for your input it was very much appreciated!!!

[Wim Decorte]

Also it looks like you will calculate and summarize data every time the report is run, even thought the data is historic and fixed.  Why not calculate the summary through a nightly script as soon as the time period (month?) is done and only report on that data