Huge field searching

[dmadill]

Hey Forum Friends,

I have about five FM5 files that contain at least 85 fields and about 303,540 records. (this is the US census data) I have a relation between all files, so you can imagine the size of this combined data...(about 500 mb). When I do some reletively simple finds, it will take 15 to 20 minutes to search. Can I do something about this? What about indexing? Also, is there a TEMP file that FMPro5 creates when doing this search? I have to make sure my computer has enough space to perform the action.

HELP...................

TestIcons.zip

[Kurt Knippel]

What fields are you searching on and in what datadase files are they located in? From where are you doing the searches?

Filemaker does create and use temp files, but I am not sure of the size requirements at any given time. If it has worked thus far you should be fine.

Keep in mind that at no time should you have less than 10% of you drive free. This is just general good sense, and to ignore will cause problems with most application including the OS.

[dmadill]

Thank you for your response, sorry I couldn't get back here soon enough.

Being an ACCESS 97 user, I could easily add the following SQL criteria:

NAMEField does not contain Miami

SUMLEVField must equal 40

PARTFLAGField is blank

COUBSUBCCfield is not blank.

But, in FM I'm having some problems. There are two files stored on my local drive that have a relationship using LOGRECNUMBER as a linking field. Again, each file contains approx. 303,234 records.

Do I do a find, a script...any suggestions for a newbee in FM

[Kurt Knippel]

quote:

---

Originally posted by dmadill:

NAMEField does not contain Miami

SUMLEVField must equal 40

PARTFLAGField is blank

COUBSUBCCfield is not blank.

But, in FM I'm having some problems.

---

What kinds of problems are you having?

Filemaker has 2 seperate Find types, a normal one which looks for specific criteria and an "Omit" mode which omits criteria from the found set. They are distinguished by the "Omit" checkbox in the status area of the request.

In this case you would have a find request where SUMLEVField = 40 and

PARTFLAGField = "". Then you would have an omit request NAMEField = "Miami", and another omit request COUBSUBCCfield = "".

Your requests would look like this on the screen:

Request One:

SUMLEVField: 40

PARTFLAGField: =

Request Two (Omit checked):

NAMEField: Miami

Request Three (Omit checked):

COUBSUBCCfield: =

icons2.zip

[dmadill]

It works!!! Thank you very much!!!

[FUBAR]

Similar problem...

I will have over 100 000 records by the time all of the data is entered and am worried about the find speed.

For the main search (locating information on a specific piece of equipment) all of the relevant information is located in the same database.

Just wondering how slow a find for a specific piece of equipment will be once all of the information is added.

Note: Actually I'm not sure if the size of the database will greatly change the speed at which finds are taken place, but I can see that the larger number or records definitly slows down my Summary fields for calculating totals.

Any thoughts/ideas?

[Kurt Knippel]

Obviously more records will mean larger indexes and will require longer search times, however it will be measured in seconds, so you may experience finds which take 30 seconds, rather than 1 seconds, but that is still a pretty minor issue.

Now unindexed searches on that many records will be abysmal, like 3 hours or something. So make sure to search on indexed fields only.