Need help with standard deviation summary field slowing down database

[aleonardo]

I came up with a solution to a problem: I needed to title a set of records (items, descriptions) from a specific volume (search result of an indexed field) which is part of a larger set of all volume items for a publishing CMS. I used a standard deviation summary field to control the display of a conditional title to let the user know that all the records of a selection had the same value in one field; if the volume number is all the same for all found records. If the deviation is zero for that field then I know all the records have the same value for that field and it conditionally displays the title in the header that shows that value (volume number). If it is anything other than zero (a deviation) then a generic title is displayed letting the user know it is a mixed set of items across more than one volume. Works great on a single user setup but once deployed on the server that summary field (and two others) for that table slows down the system unbearably and forget about connecting to the server over the internet, the table takes over a minute to create the indexes and display. Once the indexes are created the system runs ok but the initial wait is way too long. I am dealing with over 14k of records. I can live without the other summaries but I would love to come up with another solution for the titling of search results. Yes, I know I could do it as a portal filter in FM11 but I need to have this list layout as well. I setup a series of buttons that do standard searches for volumes as well which also clue the user about what volume they have a selection of (or sub selection of) but I need a way to test all search results to see if they are all of the same volume of not. A looped script may work but I doubt it would be much faster than the summary field. Anyone have any ideas. I think I am just stuck looking at this problem from one angle and need some ideas of a different direction to go. Please help.

[comment]

Once the indexes are created the system runs ok

I don't understand why you cannot index the field once and for all. IMHO, any solution using an unindexed field will be slow.

[aleonardo]

The 'volume' field IS set with indexing on but the system takes too long doing the summary calculation for the standard deviation. All fields referenced by the summary calculations are indexed.

[comment]

How about sorting by the Volume field, then comparing the first record with the last one?

[aleonardo]

That's a good idea, but when someone re-sorts the selection the comparison will not hold and the labeling will be incorrect.

[comment]

I was thinking you would make this part of your scripted search, and store the result in a global field/variable.

[aleonardo]

maybe, but still want something that can quickly tell if a set has all the same value in one field, and what that value is. The standard deviated summary works all the time without having to keep track and run a script. Anybody have any other ideas?