LONG search and index times

[Rigsby]

This is a difficult one to answer without having the exact setup. Firstly, I don’t buy what this so-called “expert” is telling you. I tend to do all my programming on a PC, but have to develop for clients running Macs, so I have a small network here running two really ancient power Macs. The reason I’m telling you this, is that between my 64 MB RAM Power Mac with only 650 MB hard disk, and a Pentium III 800 with 512 MB RAM, there is very little difference in searches, as this depends very much on the following:

The server – not the client

The size of the files (i.e. indexes)

And more importantly, the network (this involves network speed, 10 or 100 base, cards and of course number of users especially if a lot of users are doing complicated searches, or changing lots of records at the same time.)

So, just a tip here, but what I think your problem(s) could be – but I’m only guessing here – are:

An IT Expert who doesn’t know a *******, or give a ******* – so just gives you jargon answers.

A lot of people doing lots of stuff in one database.

And – now to the technical stuff –

You're not running the server version of FM on your host - big problem.

A badly designed database which is relying on users performing finds on non-indexed fields

A badly designed database which is letting users perform “cross-file searches” and this is my big tip, so let me explain:

You have two files – “Clients” and “Sales”, in Sales you search for all sales over $5000, between 1999 and 2000. No problems – both of these fields are in your “Sales” DB and should be indexed and FM runs through its index and pulls them out.

Now you do the same search, but add to the same search only sales to clients in Boston, and the city name is a related field pulled into “Sales” from your client database – at this point, FM has to create a new “Cross-File Index” which with over 300,000 entries will cause your employees to set of their searches and then go home LOL.

So, in other words, always try to avoid searches that search more than one file. Sometimes we have no choice, but usually we do! Just ask your self, the next time your performing a search, where are these fields – or the information they are storing – really located, if they are not all part of the same file, you are going to have problems – ask your “expert” about this and see what she says – if she can’t or won’t help, give him my mail address!!!!

Rigsby

[JPaul]

As i usually tell to all 'networked users', do an EXTENSIVE use of the "FLUSH CACHE TO DISK" statement at the end of each sort / search /close script !

This may dramatically change the performance of a shared db.

Try this before introducing structural changes to dbs or before to change hardware.

Regards.

[MitchJohnson]

Thanks for the great input...I will run these items by him and see what he says!!

Thanks so much...I KNEW it wasn't a "Mac" thing .

[ September 19, 2001: Message edited by: MitchJ11 ]

[MitchJohnson]

We have a database of over 300,000 customers. We recently had an independent contractor redesign our invoicing system. He told me that on a PC these searches and indexing of large parts of the database don't take much time at all but on our Macs it takes an ungodly amount of time.

We are running G4's running OS 9 w/ 400 mhz processors with 192 mb of ram over an ethernet network. The size of the individual customer profiles is relatively small so the amount of data that filemaker has to weed through is relatively small.

I have maximized the ram allocation to filemaker to the amount that tech support said would be enough (which is 45 mb) she said that anymore ram allocation wouldn't make for much speed improvement.

Filemaker is the only program our host computer runs aside from the system.

Is there any other way to speed up these functions, is something getting crossed up in the searches, etc.

Any ideas would be appreciated.

[ September 19, 2001: Message edited by: MitchJ11 ]