Speed comparison - preliminary

I’m working with larger database, here’s some preliminary feedback on the find dialog box:

Database A
166K records
Find unique string using “contains”:
Pan X: 6.09 sec.
Pan 6: 1.98 sec. (3x faster)

Find unique string using “is equal to”:
Pan X: 2.64 sec.
Pan 6: 1.51sec. (1.7x faster)

Database B
1005K records
Find unique string using “contains”:
Pan X: 27.21 sec.
Pan 6: 3.89 sec.(7x faster)

Find unique string using “is equal to”:
Pan X: 10.31 sec.
Pan 6: 4.67 sec. (2.2x faster)

So far, Pax X seems to slow down geometrically with database size and doesn’t like “contains” as much as Pax 6 did. This makes it problematic with larger databases. I didn’t go beyond 1 mill records in this test, felt 27 seconds to find something was already too long…

I also miss the “insert field” and “insert function” popups in the find dialog!

There is a lot of overhead involved in working with Unicode text instead of simple 8-bit ASCII text. There is simply no way around that.

If a user does not care about unicode, as some may not, can they have speed as their priority?

Personally, I want speed and unicode. :wink: ]

Robert Ameeti
(949) 422-6866

Unicode isn’t a priority. It’s just the way it is. There is no option to use any other character set. If there were, Jim would need to write two versions of everything that handles text, one for Unicode, and one for Mac Roman, or whatever.

Perhaps ‘priority’ wasn’t the correct word, but maybe I should have said ‘preference’.

For me to write Søren instead of Soren might be more polite, if it takes me 10- 20x longer to do my work, I will tend to choose to use Soren. As the original posted noted, users have choices. I do understand that this might require a separate set of code but that merely opens the door for Panorama X, and Panorama X Pro.

Robert Ameeti
(949) 422-6866

It would actually be much worse than two versions, because the two encodings would have to work together seamlessly at every point that text is handled. And every place where text touches the system (for example display or editing), it would have to be converted to Unicode. This would probably be a project that would take more than a year, all by itself – doing nothing else. And during that project Panorama would be completely broken, there would be no way to release interim versions.

I hope there are some performance optimizations that can be applied down the road that will increase the speed somewhat, but it’s not likely to ever be as fast as plain ASCII was.

Keep in mind, by the way, that other databases won’t do a “contains” search at any speed, not to mention functions like sounds like, regular expressions, and other formula based searches.

Huh?

MySQL does do %contains%, SOUNDEX, and REGEXP and also handles Unicode.

I’ve not done any timings for comparison as Panorama X is not yet ready to be timed.

Robert Ameeti

I’m thinking I misread your comment as I’ve found these…

Contains:
http://www.filemaker.com/help/12/fmp/en/html/find_sort.5.6.html http://www.filemaker.com/help/12/fmp/en/html/find_sort.5.6.html

Regular Expressions
http://filemaker-plugins.com/features/regular-expressions/ http://filemaker-plugins.com/features/regular-expressions/

SoundEx
FileMaker Custom Function: Soundex ( myfield ) https://www.briandunning.com/cf/1773

Robert Ameeti

Ok, my bad. Let me rephrase that – operations like contains, soundex, regex, or any formula based search cannot be handled with an index, only with an exhaustive search of every record. For disk based programs, the difference in performance between an index based search and an exhaustive search is very large. In the past, disk based database programs simply didn’t allow for exhaustive searches because of the performance issues, but I see that now some of them do.

Here is a Stack Overflow post that discusses this:

The answer on this page by Johan is especially on point. Notice that he mentions that:

SELECT * FROM t WHERE a LIKE '%a%'

cannot use an index – this is the same as Panorama’s contains operator.

By the way, this page seems to indicate that Regex in MySQL doesn’t support Unicode:

https://dev.mysql.com/doc/refman/5.7/en/regexp.html

As for FileMaker, it indexes based on words in the data. So searches based on words (or beginning of words). Any other kind of search will not use an index and will be very slow.

It’s not clear to me that the FileMaker regular expression plugins you linked to can be used for searching. They talk about applications like parsing email addresses, not searching. If they can be used for searching, they will be very slow.

I couldn’t really figure out the Soundex plugin for FileMaker, I’m not a FileMaker expert. It does mention “similar sounding content in your database”, which sounds like searching. But it seems like it just takes one text value and calculates a number. Perhaps you could make a calculated field based on that, and search the calculated field?

The bottom line is that because Panorama is RAM based it is much faster than disk based databases at exhaustive searches (searches where every record in the database must be examined).

This topic is now almost a year old but I’m reviving it anyway.

I just ran a couple of tests with a large database (675,000 records) and a select in each of three fields takes 4 seconds in Panorama 6 and 17 seconds in Panorama X. In a database of 10,000 records, the numbers are 2 and 4 seconds.

In both cases, Panorama X is two or three times slower than Panorama 6 BUT, not a lot of databases are as big as 675,000 records and, in the case of the smaller one, it’s not the factor that’s important, it’s the fact that Panorama X takes just two seconds longer that Panorama 6. Two seconds isn’t a long time in anybody’s life.

I’m finding lists objects with a search are incredibly slow on larger databases (30k records or more). It’s almost unusable. I have yet to export and reimport the records to see if something happened in the conversion, but it happens with every list search I have created in Panorama X with a lot of records in the file.

Try closing other windows – especially the data sheet. If the data sheet is open at the same time as a list or matrix that is linked to the same database, they seem to interfere with each other and cause beachballs. Closing the data sheet eliminates the problem.

There is only one window open. It is the form window with the list.

I do a fair amount of date searching, and I’m seeing similar performance drops. Should search/select be faster when dealing with dates?

Example:
90K records
Select («Done» => date(|||today|||)-1)
Panorama 6: about 2 seconds
Panorama X: about 9 seconds

Is there a way to speed up non-text searches?

(Sorry to post to this old thread - I couldn’t find a way to post a new topic. Is it because I haven’t gained the trust level yet, or am I just missing that part of the forum UX?)

I think you have just overlooked something. To start a new topic you need to be in the main index page, rather than inside a topic. In the upper right hand corner you should see something like this.
28%20PM

You won’t see my picture of course, but you should see + New Topic. You just click on that.

That said, your post looks like it’s on topic to me.

I think you could probably speed it up like this.

let yesterday = today()-1
Select «Done» => yesterday

That way, you aren’t calling the date( function ninety thousand times.

I stand corrected. I have another account, using another email address. I just gave it a newcomer’s trust level, and it can’t start a new topic. I wasn’t aware that that was one of the limitations.

Hi dave:

Thank you! That does indeed speed things up considerably - using your version, the Select seems to run about as fast - or possibly even a little faster - in Panorama X. I never would have thought about the fact that the function is being called repeatedly. That’s a very helpful insight.

After I posted, I discovered that I had another Select script that also runs very quickly, even though it’s text-based. I’m not sure why that one’s running so fast.

I wonder whether it might be helpful to create a sticky forum topic to collect tips like this so everyone could benefit from whatever approaches might speed up any performance issues in Panorama X.

Thanks for the help!

It also depends on what the function is and how you are using it. For example,

select Done => today()-1

would be MUCH faster than

select Done -> date("today")-1

The reason the date( function is relatively slow is because it has to parse the text. The today( function just immediately calculates the date, no parsing needed. I’m not sure if using today( directly would be faster or slower than Dave’s suggestion of using a variable, you’d have to experiment. But both of these would be way faster than using the date( function.

I too have been finding much longer search times with PanX. File A is a list of 500 unique suppliers each with an a unique numeric ID. File B is a database of transactions - approx 200k records for the above suppliers. There is an ID field that corresponds to File A.
In file A, I a textlist that does an automatic lookup from File B of all the transactions that match that supplier. If the ID field is text, it takes roughly 30 seconds to pull in the relevant data. However if the ID field in both databases is numeric rather than text it only takes about 10 seconds. Still much slower than Pan 6 but workable for me.
I assume this speed difference relates to the unicode issues referred to above, that it is faster to compare numbers than text.

If your formula for the text list object involves significant on-the-fly calculations you may be able to speed its display. Pre-run those calculations and store the results in an additional field. Your TLO display may build faster just looking up that field, instead of first running the calculations on every record displayed. The extra field could be populated via formulafill when time isn’t critical, and/or maintained via automatic calculations whenever File B changes.