Very disapponited by FM search engine

[Paolo]

I have a database with several records.

Among the other, field A is indexed, B can't be.

Very often I perform a find inserting criteria on A and B.

for example A=192 B>0

Unfortunately (and without any logic) FM first searches for the records matching B>0 and then constrain the found set to the records that match A=192.

This way it takes 10 seconds on 30000 records. If it would operate the opposite way (searching for the indexed field first and non-indexed after) it would take 0,1 seconds.

In fact the indexed search would be performed very fast. The unindexed, that is slower, would be then performed on a smaller set (20 records VS the whole 15000 records file)

Is there a way to force FM to search for indexed fields first and unindexed then, or I have to break the query in two steps each time?

[andygaunt]

Thinking off the top of my head, but can you script this?

I am going with the fact that in FileMaker Pro 6 you can do a find on your Field A and then do another find on top of that and thus constrain or expand the found set using the new search criteria for field B.

So, first if you do not have FileMaker Pro 6..... ooops!. Otherwise this may be a way to go.

[Paolo]

yes, but this way I have to break the query into two queries. (very annoying)

enter find mode

set field

perform find

enter find mode

set field

perform find(contrain)

and I lose compatibility with fm 5.5 that haven't the "constrain found set" option on perform find.

also, non scripted searches (just sending the user in find mode) cannot automatically be broken in two

[Paolo]

OOOPS, I should have answered no!

---

I've made some tests.

Breaking the query in two

(

enter find mode

set field

perform find

enter find mode

set field

perform find(contrain)

)

doesn't help.

It seem that when the second find is performed FM restart doing the whole thing from the start (off course in the FM way: unindexed field first).

[Ugo DI LUCA]

Paolo said:

Very often I perform a find inserting criteria on A and B.

for example A=192 B>0

If it is often with this kind of structure, then you may change the way you're making find with :

In the Main.fp5 :

1. g_CriteriaA

2. g_FoundSet

3. g_CriteriaBVariable

4. g_LastMatch (number)

5. n_constant (autoentered num = 1, indexed)

Relationships :

MainIndexedRel ---> Main.fp5:g_CriteriaA::Related.fp5:FieldA

FoundSetRel --->Main.fp5:g_FoundSet::Related.fp5:Record_ID(text)

ValueList :

"IDsList" related List of IDs using 'MainIndexRel' relationship.

In the Related.fp5 (or in Main.fp5 with SelfJoins)

1.n_constant (autoentered num = 1, indexed)

2.c_unstoredBoolean = FieldB>::MainByConstant:g_CriteriaBVariable)

Relationship

'MainByConstant'---> Related::n_constant::Main:n_constant

Back in Main.fp5, define the and use the 'FoundSetRel' to be sorted by the 'c_unstoredBoolean' .

Then a script :

SetField [g_Foundet, ValueListItems(Status(CurrentFileName),"IDsList")]

SetField[g_Last, Sum(::FoundSetRel:c_unstoredBoolean]

If[g_Last = 0]

Show Message["No records matched your criteria"]

Else

GoToRelatedRecord ['FoundSetSorted'-ShowOnly]

PerformExternalScript[sortAndOmit]

where the 'SortAndOmit' external script does :

GoToRecord [::MainByConstant:g_Last]

Omit Next

GoToLayout['Theoneyouwant']

This may sound a bit tedious to set up, but if really you're triggering this same find process on a regular basis, you may have a look at it.

HTH.

[Ugo DI LUCA]

Of course, if a portal is sufficient, the whole setting would be easier.

SetField [g_Foundet, ValueListItems(Status(CurrentFileName),"IDsList")]

A portal with relationship 'FoundSetRel ' sorted by the c_unstoredCalc.

Add one field in the Related file,

c_unstoredBoolean2 = Case(FieldB>::MainByConstant:g_CriteriaBVariable, Record_Id, ""), and drop this field in the portal.

This will show the Ids or not according to Field B result.

[Paolo]

in my situation a small amount of records match the indexed search.

so the best workaround is to search the indexed field first and then run trought the found set omitting records that doesn't mach the second criteria.

as the example:

enter find mode

set a=192

perf. find

loop

if not b>0 omit record

exit loop if...

go to next record

end loop

you approach is more general.

but very very complicated for a such simple thing like just searching two fields.

[Anatoli]

Try to script the query -- it is always good idea not allow users to do something directly. I've always disabled all direct menus.

Maybe to find A and script which will omit B =< 0

Sometimes looped scripts are running faster than unindexed search.

[Ugo DI LUCA]

Paolo said:

so the best workaround is to search the indexed field first and then run trought the found set omitting records that doesn't mach the second criteria.

but very very complicated for a such simple thing like just searching two fields.

It could be interresting to test it Paolo.

Your approach is a "Loop and omit"

Mine is a "Sort, Go To Last matching record" and omit all others...

I'll have a test of both this evening, with different sets of matched, just curious which one is quicker...

I'll drop the test here.

Now, there is a quicker solution I think, just reusing the

c_unstoredBoolean2 = Case(FieldB>::MainByConstant:g_CriteriaBVariable, Record_Id, ""), and droping this field in a layout with that field only.

Script

GoToRelatedRecord(MainCriteria)

Perform External Script

---GoLayout[Where c_unstoredcalc2 is]

---CopyAllRecords

GoToLayout[Where g_Foundset is]

Paste

GoToLayout with Portal or GTRR from g_Foundet to the Related file.

[BobWeaver]

I ran into the same situation as Paolo doing a search on an indexed field and an unindexed field where the indexed search should have eliminated all but a few records. Unfortunately, FM searches ALL unindexed records no matter what you do. Even with FM 6 doing two separate searches first on the indexed field, and then doing a constrain search on the unindexed field, the second search actually searches through all records in the file. This is absolutely unbelievable.

So, the method of searching only the indexed field, and then looping through the found set to check the unindexed field is probably the fastest workaround.

[Ugo DI LUCA]

I must be missing something here Bob....

Here's the test I've run.

1. I've randomly created a line item populated with ProductID, Qty, Price, CustomerID and InvoiceID.

10,000 items for 3,000 invoices and 50 customers.

2.I've reproduced the common error with an unstored calc in the Invoice File:

TotalInvoice = Sum(::LineItemByInvoiceID::TotalItem)

and populated randomly a Paid field with either nothing or the TotalInvoice so that I've ended with :

c_AmountDue = Paid-TotalInvoice (unstored).

3. Then set different find processes where we'd be searching for those invoices dues for CustomerX, using a ValueList of those Customers in the Invoice File.

- One with Classic Find script

SetField [Customer_ID, g_Customer_ID]

Insert Calculated Result [c_AmountDue, ">0"]

PerformScript

- One with Show All script

GTRR[g_CustomerID::CustomerID-show only]

GoToLayout X [with only field c_ReturnInvoiceID = Case(Due>0, InvoiceID, "")]

CopyAllRecord

GoToLayout[Main - where g_IDS is]

Paste[select-g_IDS]

GoToLayout[Portal with relationship g_IDS::InvoiceID]

- Last with GTRR&Omit

SetField[g_last, Sum(SelfByglobalCustomerID::c_Boolean)

where c_boolean = Due>0

If g_last>0

GTRR[selfByglobalCustomerID-show only]

GoToRecord[by field value - g_last]

Omit Next[no dialog]

GoToLayout[Listlayout]

In the Invoice File, where the Classic Find lapsed 6 to 8 seconds according to the Customer selected, the 2 other scripts were running with delay = 0 seconds

When rebuilding the Find script to be pointed to the Line Items, the results were even more wider.

What kind of finds were you talking about that would require a loop which I didn't tested though...?

[Ugo DI LUCA]

Hey...

A little correction to the above scripts.

The 'GoRelatedAndOmit' script, since I'm using it quite often right now...

SetField[g_LastMatch,Sum(::YourRelationship:c_BooleanUnstored)+1]

GoToRelatedRecord[YourRelationshipSortedbyBoolean-show only]

GoToRecord[byField Value, g_LastMatch]

OmitNext[NoDialog*]

GoToLayout

* When defining the OmitNext, hit the define option and enter 90000 in it, or whatever number you think should be the max value. Close this box and check the "Nodialog". FM will reset the value to the Max value in your file.

Ernst, since you drove me to this a while back, thanks again.

[BobWeaver]

Good point, Ugo, that is a quick way to do it.

In my case, I was able to eliminate all but about 100 records searching the indexed field first, then loop through the found set to omit the ones that didn't match the unindexed field criteria. Since this only took a couple of seconds to complete (compared to the 10 minutes that the old method took) the user was satisfied.

Still, I agree with Paolo. It's almost criminal that FM would use such an inefficient method to do a search, forcing developers to use workarounds.

[Vaughan]

I have a feeling that the Perform Find [constrain] step is *really* doing a Modify Last Find then AND-ing the new find criteria. Which means it has to index the fields searched upon.

[Anatoli]

Stephen is calling FM staff frequently "clowns". Wouldn't you agree?

[Ugo DI LUCA]

Anatoli said:

Stephen is calling FM staff frequently "clowns". Wouldn't you agree?

Circus' program pleaaaase....

[djgogi]

Be realistic.

What is the "CurrentFoundSet" ?

For FM file it is only the list of record's IDs.

So how could we search (faster) for something that doesn't really exist.

Since data needs to be indexed to be searched (the temp file will be created to allow the indexing and searching/sorting) they had only few choices

1)Loop on founds ID (workaround proposed here, but probably very hard to implement at application level)

2)Create index and then search on it (the implemented)

3)Change completely the structure and architecture of FM (hence create another product) to implement more efficient (RAM) based search and sort algorithm

Dj

[Paolo]

Goran, I don't agree...

There is no excuse for this kind no-optimization.

The same way, there is no excuse for many other mis-features and bugs, in a professional and well paid product like filemaker.

What does PRO stands for? problems?

Offcourse we still think FM is great for many reasons, that's why we are here. Too bad we have to face so many problems too everyday.

My opinion on the workaround found since now:

looping and omitting is good if the records (after the indexed search) are no more than 100, 200.

otherwise Ugo's solution works better, but it a lot more complicated!

Thank you very much to everybody for your replies!

[Ugo DI LUCA]

Paolo,

Dj made a good point here IMO. The problem still stands on the number of uninexed records you may hold in a db, thus leading to this work-around.

Seem to be you already opened a really good thread on these standards a while back.

Now, about when to use one method rather than the other, I'd have another interpretation.

I'd rather use :

- a "FindandLoop" script when the criterias could be variable (>0, <0, >100, >500,...)

- a "GtrrAndSort" when the criteria may vary because of a global field(A*global >0) - just guessing here !

- a "ShowAll" when the criteria is fixed (>0), so that you won't need too many of these unstored "ShowHideCalcs" at Left side.

The number of records isn't in my opinion a correct criteria, as a ShowAll will definitely be *always* quicker than a Loop.

[Anatoli]

Goran, if this is the only problem with FM then OK. But the list is growing every day. Like many texts functions are not working with another languages. Years! The whole internal code is not consistent and if their FM programmer for that module left, nobody is taking over the old and persisting problems and nobody will repair them.

That sucks.

I hope the FM 7 or X will be better. But because date isn't know and after FM 7/X will appear I will be still 2-3 years from move.

Just to be on sure side, I am learning MySQL and Lasso.

[djgogi]

Actually, I wasn't defending FM team.

My intention was to simply put it clear where is the problem with current (future) version of FM.

As I said, it's a legacy problem, you have certain file structure which works very well in most cases and is very difficult to take decision to recreate all from scratch.

(Right now there is almost no difference between FM 1.0 file structure and FM 6 file structure, of course new elements are added, but basically it is the same).

One thing: I said that the looping workaround is hard to implement at app level. I mean as internal FM code (given file structure as is right now)

Any way, I'm agree with all of you that to be competitive, FM must solve many speed issues, like sorting and searching, operations that should be performed directly in RAM and not thru temp files.

Dj

[Anatoli]

Yeah sure, you are right in problem analyzing and describing. It is the same, like with core of MacOS. 20 years old with glued functionality. And as with MacOS the changes are long overdue in FM.

[BobWeaver]

I agree with Dj up to a point. If you are trying to constain a found set doing a search on an indexed field, then I can understand that FM might have to search the entire file because of the way the index is constructed. But, if it's doing a search on an unindexed field in a found set, FM isn't using an index, and should have enough intelligence to only look at the found set. In fact, even if it's doing a completely new find to replace the found set, it's only sensible to search the indexed fields first and then the search that partial result in the unindexed fields.

<<Didn't see this second page, or Dj's second comment when I posted the above>>

Yes it's probably a legacy problem. It doesn't look like FM has made any changes to the actual database engine in many many years. Versions 5.0, 5.5 and 6.0 are essentially the same thing with a few minor ornaments glued onto the front end.

[Paolo]

Anatoli said:

I hope the FM 7 or X will be better.

The more FM7 will be innovative the less it will be compatible with FM6.

Probably if you will need to upgrade existing solutions either you will have to stay on FM6 or rewrite them from scratch to port them to FM7.

[Anatoli]

Sure Paolo, that's why I wrote:

I hope the FM 7 or X will be better. But because date isn't know and after FM 7/X will appear I will be still 2-3 years from move.

Just to be on sure side, I am learning MySQL and Lasso.