Please if somebody can help me!

I have database of 1.6 mil subscribers (cca. 1.2 Gb, 60 fields, FileMaker
Developer 7) - where I want to put average amounts (of 7 different revenue
types) from 6 different databases (each database has monthly revenues).

I have two options:
1. Make REPLACE script to calculate one-by-one column, or
2. Make LOOP script to calculate one-by-one row.

Each of this two ways start to work fine (REPLACE: 1000 records per second,
LOOP: 30 records per second), but after some time the speed decreases to few
records per second.

I don't know what to do! I have Workstation with two 2.4 GHz processors, 3
Gb RAM, SATA HDD's with enaugh place for temp files... Do I need to set up
Edit - Preferences - Memory to higher or lower memory, and how to set up
Save Cache Content (idle time or on intervals)?

Please help!

I have encountered such a problem, and I just split the file by script
into several sets of records where the processing speed is still high.
When you think of it, it should not be too hard to set up.
Just my 2 - euros - cents.
Remi-Noel


"Beetle" <beetle (AT) hi (DOT) t-com.hr> a écrit dans le message de news:
dd0l12$f7p$1 (AT) ss405 (DOT) t-com.hr...

I would rather like to split the file, but at first I need to do following:
1. I need to collect all amounts per each subscriber
2. Sort all subscribers by total amount
3. Then put mark for each 10% of database (I'm doing segmentation by
revenue)

This is impossible to make on different databases, while I don't know what
revenue is criteria for each of 10% of subscribers. So, first two tasks is
simple (but slow). Third task I made on following way:
- In sorted database I've created a field 'Mark' and then run 'Update of
serial number'
- At another database I've created 10 records where are 1.6 mil numbers
splitted to each 10% with their upper and lower level (each group of 10% has
160.000 of numbers with appropriate mark: 10%, 20%,....)
- Relationship between master database with 'one-to-many' relationship,
where each record at master database responds as:
- Field 'Mark' (master file) > Field (Upper Level)
- Field 'Mark' (master file) < Field (Lower Level)
- In addition, everything is just updating at master file a separated field
'Decile' (which 'Mark' field responds to 'Upper' and 'Lower level' mark of
another database).

Main purpose for remaining all records at one database is further usage of
the same database at Cognos (Business Intelligence software), where is
'piece of cake' making calculations based on good segmentation.
Issue is not logicaly way (everything is automated), then decreasing of
speed of execution. At beginning, all scripts (based on REPLACE) doing cca.
1.000 records per second, and after short time - 10 per second, and then 3-5
per second.

Maybe is possible to make another logicaly procedure that wouldn't decrease
the speed, or there are some 'tricks' how to set-up memory and file cache
options at FileMaker.

Sorry on long posting, and please help me.



"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote

Now that I see somewhat better what is your application and your dealing
with an enormous file, I would try to do the following (non tested) :
- I suppose your main DB "A" is a built out of several much smaller
files, piled up into one, and that each subscriber has a definite ID or
serial number, and that all the needed fields are indexed at creation
and/or updating.
- I would capitalize on the high speed FMP functions for a file of any
size like finding a set of records, exporting them, getting related
fields contents.
- "to collect all amounts per each subscriber" : easy to make (and quick
to run) with the 'SUM' function and a relationship based on the ID.
- Then, I would roughly and arbitrarily create 100 levels of numbers,
say : less than 1000, between 1000 and 1999, 2 to 3000, ..., 99000,
above 100000, or somewhat different depending of the expected scope of
revenues.
- Create 100 files as exports of the main DB "A", say "A005" is the
export of the "A" records where the revenue "R" is between 5000 and
5999.
- For each file, do the sorting by "R" and fill up a field named "token"
with the contents : 'FileName & n', with 'n' being a sequential number
of that file (so 'n' should be less that 20000).
- Create a new file "B", as the sequential import of each of the 100
sorted files. Then - if I am not mistaken - you get "B" equals a sorted
"A".
- Divide by 10 the total number of records of "B", say it gives the
number "D".
- Do a 'find' of each 'decile'. To do that, as you know the number of
records of each of the files "A...", you may add up those numbers of
records of enough sequential files untill the sum becomes above the
number "D", and run by script the counting of the last file records
until the total reaches "D". Then you may take advantage of this by
numbering a sequential number that would, at the end, span the whole DB
"B".
Is that the end ? I don't know if the above make sense, but if it does
it should be not that lenghly in processing time.
Could you please keep me posted ? and of course tell me if I can help.
Remi-Noel
PS : I don't know if to change drastically the cache memory size would
have a big effect on processing time. I don't think so. On another hand,
there is a 'emptying cache' script instruction.


"Beetle" <beetle (AT) hi (DOT) t-com.hr> a écrit :

I said : "fill up a field named "token" with the contents : 'FileName &
n', with 'n' being a sequential number
of that file".
I think you should use "FileName & " / " & n", with 'n' being a
sequential number, which allows you to let the "n" be a number ie with a
variable number of digits - 9 instead of 00009, 888 instead of 00888,
.... -.
Remi-Noel

Hi Remi-Noel Menegaux,

Thanks a lot for your help. I will try to do as like you described.
As soon I will finish, I will let you know how this is working.

Best regards,

Krunoslav Celing



"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote