Speed up a summary calculation

[Atalanta]

To simplify my question: Is there any way to do this summary faster?? It takes about 4 hours on a win 7 machine in FMP 11 ADV to do 10,000 records.

I have a field I'll call Widget.

I have a summary field WidgetCount (count Widgets).

I have a calculation field WidgetCalc with the formula: (getsummary(widgetcount; widget)/widgetcount) which gives me a percentage of each widget to the total number of widgets.

I was asked to give a report with it sorted by most popular widget. The full report, I gave up on waiting for it to do the sort on WidgetCalc for over 300,000 records. I just turned it in sorted by widget and let them suss it out.

I narrowed it to 10,000 widgets and it's about 4 hours grinding that same sort. Is there a way to do it faster with the full widget count? Course it's also a shared file and when I tried to do it over wireless, it said, "no thank you".

[Aussie John]

Im not sure if this would be quicker but I suspect it might.

Create a self join relation using the widgetname. widget1 and widget2

Change the widgetcount summary to a calculation using count(widget2::widget) with "evaluate from context of widget1".

Calc the percent as a simple calculation and not a summary ie widgetcount/get(foundcount)

Create a concatenated calculation widgetcount&widgetname

In the summary report sub summary by the concatenated calculation.

[Atalanta]

Im not sure if this would be quicker but I suspect it might.

Create a self join relation using the widgetname. widget1 and widget2

Change the widgetcount summary to a calculation using count(widget2::widget) with "evaluate from context of widget1".

Calc the percent as a simple calculation and not a summary ie widgetcount/get(foundcount)

Create a concatenated calculation widgetcount&widgetname

In the summary report sub summary by the concatenated calculation.

Unfortunately, creating data fields is not a viable option. I can carefully create summary and calculation fields, since they don't affect imports. I'm doing this on a clients solution so have to make do the best I can with what they have.

I'm guessing this is harder than I thought.

[comment]

How many unique widgets are there in the entire population of 300,000 records, and how long does it take to produce a report sorted only by widget and reporting ONLY the count of each group (i.e. no calculation field, just the one summary field)?

[Aussie John]

Unfortunately, creating data fields is not a viable option. I can carefully create summary and calculation fields, since they don't affect imports. I'm doing this on a clients solution so have to make do the best I can with what they have.

I'm guessing this is harder than I thought.

Except for the last field I suggested (which was to help the summary) you would not need to create any new fields - data or otherwise, just changing the summary fields (which you already have) to calculations based on a self join relationship.

[bruceR]

If you use a loop that freezes the window and captures the data into variables first, then performs analysis on the variables, it should process about 100,000 records a minute.

[Atalanta]

How many unique widgets are there in the entire population of 300,000 records, and how long does it take to produce a report sorted only by widget and reporting ONLY the count of each group (i.e. no calculation field, just the one summary field)?

Running it straight out with just the summary and calculating the percentage (not sorting on either) runs quickly (quicker than getting a cup of coffee).

When I narrowed the set to about 4,000 records (the 300,000 is narrowed from 800,000), there were 817 unique widgets. There probably aren't that many more in the full set, maybe 1,000 or 1,500. I'm narrowing it by date, but not running any sorts on the date.

I'm trying to see how much faster running it locally is, I did all the finds I needed and exported the records to a .fp7 file and am running the sort on that. Running it locally is much faster. It's taking minutes to run instead of hours on the 4,000 record set.

Edited to add:

Thanks for talking it out with me. I got it figured out. Oh and no matter how I ran the sort, it wouldn't sort the percentages properly.

Since I exported the found set to a local database, I could do what I wanted with it.

I created an additional number field (Percent). Once I did the sort (to get the summary numbers and percentages), I did a replace all of the percentages into the number field. I re-ran my sort with Percent as my primary sort and Widget as my secondary sort. And I got my report. Now to try it with the full record batch.

I may be able to script this report in a database in which I can create fields - import the records, run the report, delete the records.

Edited May 13, 2011 by Atalanta

[comment]

I think that before looking for workarounds you should try solving the real problem. I believe the real problem here is that you are sorting by the calculation field. Instead, you should be sorting by the breakfield (widget), descending AND turn on the option to 'Reorder based on summary field [WidgetCount]'. On my not-very-fast computer it takes about 15 seconds to sort 300,000 records into 1,000 groups and show the groups in descending count order.

[Atalanta]

I think that before looking for workarounds you should try solving the real problem. I believe the real problem here is that you are sorting by the calculation field. Instead, you should be sorting by the breakfield (widget), descending AND turn on the option to 'Reorder based on summary field [WidgetCount]'. On my not-very-fast computer it takes about 15 seconds to sort 300,000 records into 1,000 groups and show the groups in descending count order.

Oh, gawd. I don't believe I didn't even SEE that option.

THAT was the answer I needed.

Thank you, comment, once again you have come up with the easy answer. :thankyou2: