Position vs. Patterncount Smackdown

[Razumovsky]

So, I have this really really big concatenated list of composer + songs here, sorted by composer. Some composers are tremendously prolific (a few of them have several thousand songs) and some are one-hit-wonders. I want to return the value number of the first and last songs of a certain composer, and I want them the fastest way possible in a calc. I can also see all the composers songs through a relationship, but there are duplicates in this set (lets say due to them being on multiple albums or something), none in the list.

I can deal with workarounds, but they must be faster than dealing with the list which I already have on hand. I can not use a valuelist for this, as each song is stored in the same row of a repeater in different records (sorry, but the repeaters have to stay as well). The other rows have unrelated data that I do not want to consider.

Do I:

1). Position the first one and right position the last one.

2). Position the first one and patterncount the last one.

3). Position the first one and count the sum of a unique selfjoin through the relationship.

4). Do something else (what?)

Any thoughts?

-Raz

[Raybaudi]

Hi

can we assume that the first five letters of each row of the list are always different for different composer ? ( or better a minor number too)

How much rows will ever contain that list (about) ? 10,000 100,000 500,000 ?

[Razumovsky]

I am planning on reserving the first 20 spaces or so for the composer names in a

replace("000000000...."; 20-length(Composer); Composer; length(Composer))

kind of deal to secure the sort (there are other measures in place to keep composer length 20 or under). A Position on this 20 header will get the first one.

This will be for drop down menus, so while I usually do not like to use more than 20 or so values, certain ones would want to use FM native typeahead for searching large indexes. I would like it to have no limit beyond the size of a text field. In practice, I could settle for 32000.

*I should note that I am ultimately planning to use a repeating stored text calc to get the values. I want to shut off the recursion exactly at the last needed value, so need the last value number in the list to match with the CalcRepNumber + Start Value.

Edited July 14, 2006 by Guest

[Razumovsky]

Nevermind, I figured it out: the answer is 'yes'.

You do all three (or four), whichever is faster for the current query.