Huge 21m record FM Database - Index disappears

Hi There,

I am using FMP Advanced 8.0 and I have a HUGE database, with
~21,000,000 records and 2.0GB in size. There are only 6 fields, and I
need to index them. FM manages to get through the indexing process
after several hours (on an 8-way Intel Mac with 8GB RAM) and all looks
well and good. Unfortunately, when I go to do a search, FM tries to re-
index the field. When I go back into Defined Fields, the fields I just
indexed now appear to be unindexed.

Other then converting this to SQL Server (where it should be anyways,
I know...) has anyone any suggestions as to why this is happening and
how to fix it?

Thanks,

Sean

Re: Huge 21m record FM Database - Index disappears

sean@codexdata.com wrote:

> Other then converting this to SQL Server (where it should be anyways,
> I know...)

Why ? FMP can usually handle such little bases.
But FMP don't use the 8 procs ; only one...

I hope your RAM is high (mini 4 GO...)

> has anyone any suggestions as to why this is happening and
> how to fix it?

I usually use 6 M records in a 6GB base, and many fields are indexed
without problems in FMPadv 803 and 901 (macOSX 10.4.10 on G5).

Did you set the cache memory (FMP preference) to the max (256MO, which
is to few IMHO) ?
--
www.D-L-S.org

Re: Huge 21m record FM Database - Index disappears

How are you going about triggering the reindexing? And why do you think
the ones that are unindexed were just indexed? Are they all regular
number and/or text fields? Are you on 8.0v3? Was this file converted
from another version, or ever opened in another version that you know of?

You can try saving the file as a clone and then import all the data in
again. The saving as clone should fix/reset any indexes that are
broken. But before you go to that time and trouble, I'm wondering if we
can learn anything from my questions above.


sean@codexdata.com wrote:
> Hi There,
>
> I am using FMP Advanced 8.0 and I have a HUGE database, with
> ~21,000,000 records and 2.0GB in size. There are only 6 fields, and I
> need to index them. FM manages to get through the indexing process
> after several hours (on an 8-way Intel Mac with 8GB RAM) and all looks
> well and good. Unfortunately, when I go to do a search, FM tries to re-
> index the field. When I go back into Defined Fields, the fields I just
> indexed now appear to be unindexed.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Re: Huge 21m record FM Database - Index disappears

> I am using FMP Advanced 8.0 and I have a HUGE database, with
> ~21,000,000 records and 2.0GB in size. There are only 6 fields

With such a simple structure and the vast amount of data all the user
friendliness of FM becomes irrelevant in the light of it's relatively
weak database engine.

I am not sure what's available on the Mac; I would move everything
ASAP to FoxPro, SQL or Access even if it means to use a PC.

Re: Huge 21m record FM Database - Index disappears

TL wrote:
>> I am using FMP Advanced 8.0 and I have a HUGE database, with
>> ~21,000,000 records and 2.0GB in size. There are only 6 fields
>
> With such a simple structure and the vast amount of data all the user
> friendliness of FM becomes irrelevant in the light of it's relatively
> weak database engine.
>
> I am not sure what's available on the Mac; I would move everything
> ASAP to FoxPro, SQL or Access even if it means to use a PC.

I'm not sure that makes any sense. 21,000,000 is a lot of records, but
certainly not *too* much. You will find that searches are still pretty
instantaneous. Imports might be a little slow, as would replacing data
across all records. I guess it really depends on what the original
poster is trying to accomplish with it.

Re: Huge 21m record FM Database - Index disappears

On Oct 17, 9:42 am, TL wrote:
> > I am using FMP Advanced 8.0 and I have a HUGE database, with
> > ~21,000,000 records and 2.0GB in size. There are only 6 fields
>
> With such a simple structure and the vast amount of data all the user
> friendliness of FM becomes irrelevant in the light of it's relatively
> weak database engine.
>
> I am not sure what's available on the Mac; I would move everything
> ASAP to FoxPro, SQL or Access even if it means to use a PC.

You could certainly move it to either MySQL or PostgreSQL on the Mac,
and that certainly is a viable solution. I wouldn't go anywhere near
Access, and I can't see the point of FoxPro here. Moving to MS SQL
Server would make no sense at all.

Its *very strange* that the indexing is not working in FM.

I'm wondering if there is a problem/corruption with the database. I'd
suggest recreating the file from scratch (its only six fields after
all), making sure the indexes are turned on for the fields in
question, and then importing the data from the other file. Maybe even
export to plaintext (.csv / .tab ) and importing that.

There is no reason filemaker can't handle this application and/or
should be avoided. I suspect you just have a damaged file.

-cheers,
Dave

Re: Huge 21m record FM Database - Index disappears

Ok, so it turns out that machine was running FileMaker Pro 8v1. I
should have checked that first! Once I updated it to v3 the indexes
didn't spontaneously disappear as they were before. I am actually
surprised how quick queries run for having so many records. Thanks for
the help! I'm happy to not have to mess with SQL for this particular
project.

Cheers,

Sean