Speed comparison - preliminary


#1

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!


#2

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.


#3

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


#4

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.


#5

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


#6

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.


#7

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


#8

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
https://www.briandunning.com/cf/1773 https://www.briandunning.com/cf/1773

Robert Ameeti


#9

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).


#10

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.


#11

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.


#12

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.


#13

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