multiple find requests - slow

[jonr]

I have a script that does the following:

1. Enter find mode

2. In a loop, create a series of find requests, each in the form: "==" & (some data). The total number of requests can be in the thousands.

3. Perform find

In filemaker 6, the find took a negligible amount of time, but in 8 it takes several minutes (even when the number of requests is just 100 or so).

I've discovered that changing the requests to use "=" instead of "==" results in a quick find. That may be OK in my case. Still, it is somewhat mysterious why fmp8 is so incredibly slow compared to fmp6.

[scolesm]

Would it be possible to post your script? It would help others in answering your question.

All the best,

M

[jonr]

Sure. Here's the core of it:

Set Field [ ChemicalLibrary:_Gtempnum1; External("CAMO_GetNumFoundIDs"; "") ]

# Enter Find Mode [ ]

# Set Field [ ChemicalLibrary:_Gtempnum2; 1 ]

# Loop

# Set Field [ ChemicalLibrary::CBNumber; "==" & External("CAMO_GetFoundID"; ChemicalLibrary:_Gtempnum2) ]

# Set Field [ ChemicalLibrary:_Gtempnum2; ChemicalLibrary:_Gtempnum2 + 1 ]

# Exit Loop If [ GetAsNumber(ChemicalLibrary:_Gtempnum2) > GetAsNumber(ChemicalLibrary:_Gtempnum1) ]

# New Record/Request

# End Loop

# Perform Find [ ]

The _G variables are globals. The plugin supplies the series of search strings.

[John Mark Osborne]

I'm guessing you are running 8.0v2. 8.0v3 improves the speed of searches on unstored calculations. I believe the speed of exact match finds might also be improved by updating to 8.0v3. You can find the free Updater at:

http://www.filemaker.com/support/downloads/index.html

However, there could also be an issue with your plug-in. Have you checked compatibility with FileMaker 8?

Lastly, I'd like to find out why you need to create thousands of requests. Maybe there is an alternative solution that will work better.

[Søren Dyhr]

What does the plugin do?? Well you said so - I appologise for having missed that ! But I have a an idea of what might be the issue, from fm7+ are the field bounderies considerably larger and the indexing requires more if done fully, hence the choise of making explicit indexing.

If your field's aren't defined to hold atomic entities but rather text in several words, is the circumstantial behaviour what kind of indexing is chosen, and by choosing strict searches in not atomized field structure is there a speed loos to observe.

Even if you data is split out in atomized chunks, is 1000 fields pretty flat, to be honest.

I think I would break it out in a related file, and make ANDFIND's by using Jon Rosens technique ...I try to find it till later!

--sd

[Søren Dyhr]

Blast, I found a mirrorsite where the template was uploaded ...but it isn't working anymore on fm7+ the algorithms are simply not the same - If you have fm5 or 6 laying around check this out:

http://hyperarchive.lcs.mit.edu/HyperArchive/Archive/per/iso/iso-18.hqx

Where in there is a bonus folder containing the trick.

I'll have think further...

--sd

[jonr]

To follow up:

1. I am already using 8.0v3.

2. The plugin is irrelevant. I can reproduce the behavior as follows. First, make a bunch of records and populate a field:

Freeze Window

Set Variable [ $num1; Value:6000 ]

Set Variable [ $num2; Value:1 ]

Loop

Set Field [ test2::blah; "CBNOAA0000000000" & $num2 ]

Set Variable [ $num2; Value:$num2 + 1 ]

Exit Loop If [ $num2 > $num1 ]

New Record/Request

End Loop

Then, run the following:

Freeze Window

Set Variable [ $num1; Value:100 ]

Enter Find Mode [ ]

Set Variable [ $num2; Value:1 ]

Loop

Set Field [ test2::blah; "==" & "CBNOAA0000000000" & Floor(10000 * Random) ]

Set Variable [ $num2; Value:$num2 + 1 ]

Exit Loop If [ $num2 > $num1 ]

New Record/Request

End Loop

Pause/Resume Script [ Indefinitely ]

Perform Find [ ]

Now that's "just" 100 requests, but the find takes 25 seconds! This is drastically slower than fmp6.

3. This methodology is part of a search interface allowing the user to perform boolean queries into a relatively complicated database. It was developed before Extend/Constrain found set, and to that extent might be improved. However, I doubt that would completely remove the need for multiple requests, and at any rate I'm not eager to redo the whole thing, so I'll probably just switch "==" to "=" and leave it at that. Thanks to all.

[Razumovsky]

I might be misreading the thread, but why not use a filtered Portal? 100 find requests does sound excessive, and if you are looping them it would suggest that a many to many relationship might be more appropriate. I realize this does not really answer your question, but I think it would be much faster, and not difficult to set up.

For your last example, you would just set a global field to your 100 variations of your query term, separated by carriage returns. Create a relationship Global=Blah, and view all the matching records in a portal very quickly.

-Raz

Edited July 3, 2006 by Guest
added example

[Søren Dyhr]

Your approach isn't utilizing the power of relations, and by putting on "==" are you making the indexing a step further than actually required for this purpose, you would never search on partial strings. By storing long similar strings or you enforcing redundancy on your data, which actually only is an attribute to the data ...perhaps required by the plugin, but nothing to make a habit out of!

Alright I can make the same in half the time via the above script, and it's actually both to generate 6000 records and single out a the sample of records the 100 random number suggest (please consider the lack of uniqueness in random numbers) ...but all in all isn't it the making of a found set that takes time here!

While thinking at it, is what takes time by building request on request the validation of the input as well as commiting each request ...it happens each time your script makes a "New...."

I gave you idea a stab! The attachment to this post is something that easily can be changed to recieve the output from the plugin....

I even tried to measure if I could get a time for the scritpt to evolve, but it singles out the required records in less than a second. This means that the generation of 6000 records is the only thing taking some time to make approximately 10 seconds on my old dozy G4 powerbook.

--sd

speedtest.zip