Indexed fields and file size

[GlennC]

Running any process on my main file (~200,000 records, ~400 fields) is becoming a very slow affair. I was wondering to what extent field indexing increases file size and processing. Would things be smaller/faster with fewer indexes?

[Søren Dyhr]

Aren't you barking up the wrong tree here??? A main file consisting of beyond 400 fields - looks as if no sort of normalization has taken place, at this day of age of sortable portals as well as multilinekeys might say a calendar showing the entire year take up, when you fool around perhaps, 25 fields... I would be hard pressed to come up with 400 atomic entities!!!

--sd

[GlennC]

Thanks SD but that doesn't answer my query. The file is stripped down to minimum specs already. The design of FM6 is such that calculations involving related fields cannot be stored; the number of calc fields needed make this absolutely unworkable.

Any help with my question?

[Søren Dyhr]

Sorry It wasn't ment to sound snotty, but what you descripe is way beyond anything I've heard of as "stripped down" - I do still wonder if you data can be arranged different - such as this video demonstrates:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

...in my humble opinion does put an end to all too flat'ish structures!!!

--sd

[transpower]

Glen:

The short answer is Yes: reducing the number of indexes will reduce size and increase speed. I don't know of a quantitative method to estimate the gains; I think you'll just have to experiment. Or purchase a faster computer (CPU, drives, etc.).

[GlennC]

Thanks transpower. Thanks SD for the link - some useful ideas, but doesn't get around the unstored calcs issue.

All replies appreciated!

[LaRetta]

Hi Glenn,

While Transpower is correct in that fewer indexed fields may decrease file size, it certainly doesn't help with speed as you've discovered. Searching, sorting and summing grind a solution to dog-speed because they must recalculate all records every time.

If you are depending upon aggregate functions (Max, Min, Sum etc), you might consider writing these summaries as static data instead or incorporating something similar to Mikhail Edoshin's Fast Summaries here.

I admit I cringed at first when reading Edoshin's SmartRanges and FastSummaries. But he explains it beautifully and will walk you right through it. Another alternative is to use an event trigger plug-in such as SecureFM to increment sum totals in a Summaries table accordingly (in the moment); or run a loop at month-end to increase your summaries.

If they are simply unindexable because they are related, you might consider turning them into indexable fields. A wonderful description can be found on CobaltSky's website Automatically Indexing non-indexable fields.

I've been using a variety of these methods. You are not alone in this dilemma, as I've recently confessed in a post called Aggregate Confessions. vs. 7 helps greatly also.

LaRetta

[Søren Dyhr]

We're facing a problem Mikhail's page has been down for a little over a week now... There have been movements that would appoint him to an award of excellence at DevCon, but he refused barely on economic reasons.

He is in my book one of the cleverest developers around ...but might not get what he deserves for his altruistic effords. We ought to find a way to donate to the upkeeping of his site at least.

Now it could be another perfectly simple explanation that causes the sites absence ...but doesn't he deserve getting a little more than just accolades.

I wonder how long time Google cache would last:

For SmartRanges look here:

http://216.239.59.104/search?q=cache:mM6...anges&hl=en

For FastSummaries look here:

http://216.239.59.104/search?q=cache:J7K...aries&hl=en

--sd

[GlennC]

Very interesting ideas, thanks very much!

(I had no trouble with the site links.)

[Søren Dyhr]

No his site came up by the time I was writing... I'm so glad hi didn't pack it in!

--sd

[transpower]

LaRetta:

Not indexing speeds things up (in normal operation), but then doing a Find on a non-indexed field slows things down. So I index just those fields which will commonly be searched.

[Søren Dyhr]

Not indexing speeds things up (in normal operation)

Not entirely true, e.g. you have a relation on itemlines to show the remaining stuff of each of the items that live could show if an item is sold out, telling this say by a change of colour in the lines. If such a portal is supplied with a scrollbar and perhaps also a sort order, will you be abel to see rendering in action... this is in particular true if you network a solution. This is a info pulled a relation away and can therefore not be indexed at all.

--sd