Re: How to set up Filemaker to avoid decreasing of speed

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@xxxxxxxxxxx> a écrit :
>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@xxxxxxx> wrote in message
> news:42f5bcfd$0$29950$626a14ce@xxxxxxxxxxxxxxx
>> 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@xxxxxxxxxxx> a écrit dans le message de news:
>> dd0l12$f7p$1@xxxxxxxxxxxxxxxxx
>> > 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!
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: LDAP Performance (long)
    ... Cache the slapd's internal database lookups in slapd memory. ... The first is the new TAG:key lookup, ...
    (comp.mail.sendmail)
  • Re: Cache-Size vs Performance
    ... logarithmic decrese in the miss rate as the cache size grows ... in big database applications ... ... where the database uses real storage to compensate for disk record ... database people in stl/bldg90 and the relational/sql system/r people ...
    (comp.arch)
  • Re: ASP.Net Caching Questions
    ... As stated, the lookups have to be as fast as possible, so the idea is ... Loading the information from a file or sql column will not ... database will perform comparably to using Cache. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Running queries on large data structure
    ... ugly to convert it into some relational shape for the database) ... of time when reloading them from disk but YMMV and you would have to ... pickle it and load it later and work on the data? ... My data layer checks the cache ...
    (comp.lang.python)
  • Re: There needs to be a free Informix Express
    ... not the features. ... number of cores in my box is a feature as is the size of the database. ... worried about a loss in "potential" revenue when the actual revenues ... Any system that uses such modest resources is not hardly worth ...
    (comp.databases.informix)
Loading