Vincent_L

SPEED, speed, Speed AND SPEED

Discussion created by Vincent_L on Sep 24, 2012
Latest reply on Sep 26, 2012 by jrenfrew

The first thing that will make me quit the plattform is the Filemaker's poor performances.

Filemaker needs a deep rework of it's internal engine with order of magnitude speed increase. No, I'm not trying to compete with MySQL for web hosting. Just the speed of my 100% FMP based solution.

 

So when Filemaker will be built with speed in mind ?

 

- The related fields are too slow. One to one relationship should be very close to as fast as if the related field would be in the same table. Filemaker needs more clever related filed fetching like MySQL does with its  "explain". For instance, the order of the relationship matching fields declaration shouldn't matter, Filemaker's should optimize it itself (and it coudl be done one a day, with a server command "optimize solution", that would built the necessary optimization trees, so that optimization won't impeed live performance)

 

- The unstored calc are way too slow (a best practice is to avoid them, but doing so negates the purpose of a related database, and negates Filemaker rapid development feature).

I'm pretty sure unstored calcs are re-eavaluated too much time : if you've unstored field C, based upon unstored field B which is based upon unstored field A, A will be evaluated 3 times instead of one.

There should be some sort of instant caching.

Also if in the same calc you write something ling related::field1 + related::field1  + related::field1, related::field1 will be evaluated 3 times instead of one, of course you can fix it by using a let function.

But that shouldn't be necessary, especailly for newbies.

 

What I thing would help is a kind of automatic let function : whenever a Record is displayed, all unstored calcs involved in that display, would be evaluated and cached just for the time of the display, using a

an order tree that Filemaker would build when FMP structure is updated. So in the previous example, Filemaker would know it has to evaluate A first, cache it's value in ram, and then on subsecquent calls get that cached value.

 

- Often, unstored calc are just depending on related static values that don't change often, so maybe we could have an auto-re-evaluation of the depended fields whenever a used field is changed. So in my previous example, changing the value of A, will automatically trigger the update of field B and C and those would behave as if they where stored. I know that some advanced Filemaker user use that techniqu

will script tiggers but that's a nightmare to implement and maintain, and it can be slow if many fields and records depends on one, but that slowness is mostly due to the fact it's not native.

 

- Imports and exports are too slow

 

- Deletes are too slow. Also we should be able to truncate a table instantly (actualy I've a script that takes 1 hour on a powerfull SSD server just to delete all record of a table).

 

- Replaces are way too slow (and we need to be able to replace multiple fields values in the same replace). It's often faster to export and import rather than use the replace function if you need to change any fields.

 

- Scrolling list view and table view is too slow (not only the 12's bug, but whenever there's related fields / unstored calc fields they become unusable)

 

- Finds involving unstored fields are unnecesserally too slow and this is something that could even be fixed in 12vX point release : Let's do a search with 3 fields involved Store Field A1, Store Field B1 and unstored field C1. Filemaker will evaluated all the fields C of all the records of the database, even though A1 and B1 criteria alone would result in only 3 records. Experienced developpers will of course do the search in 2 steps, search for A1 and B1 and then constrain the search on C1. That will be massively faster. It can be scripted, so why Filemaker doesn't do it by itself ? Especillay for newbies and expecillay since the quickfind feature appeared.

Of course Filemaker internally could even do better in FMP 13 : look with of the indexed field has the least record, and then optimize it that way : There's 400 records with A1 corrsponding to the search criteria, and only 3 corrsponding to B1. So Filemaker will do 3 search, B1 then constrain on A1 and then on C1. It could do that optimization on the fly when in find mode, or, even better for scripted search it could use search strategy tree that would update once per day with the aformentionned "optimize solution" feature, that would count the different record counts for the different scripted criterias (of course it would ignore criterias involving a variable).

 

- Freeze window and blank form view shouln't be optimization : A filemaker trick to get non terrible performance is to switch to a a blank for view layout and use the freeze window script step, that shouldn't be the case. All script should be computed off screen, display shouldn't be involved at all, of course except if there's a refresh window display step. That's a trap for newbies, and it litters the code of developpers. So All script should be computed off scren unless a Refresh display window step would be triggered. That means eggting rid of old filemaker depencies that involve the display which will be very good for server side processing anyway.

 

- Much more should be done on the server : At least all scripts steps that are actually writing to the database, should be performed by the server automatically. For instance if a client triggers a replace, the replace should actulaly be done on the server automatically without a line of code specifiying it. New records, set fields, imports, Execute SQL (when we'll get the much needed write function).

Of course, searchs, Execute SQL Reads (like select)  and maybe sorts. But for the read parts maybe operation that do not Extend the found set will be best performed by the client (for instance sorts, and constraining searchs). The marvelous FMSDIFM http://www.1-more-thing.com/FileMaker-12-Video-FMSDIFM.html shouldn't need to exists. It should be the native way.

 

- Much better WAN performance (this will imprive LAN also) : of course the previous point helps, but that also means much less chatting between FMS an client. Maybe it's time that when loading a record only the necessary fields gets loaded and not all the fileds of the record (here again my optimize solution tree could help).
But I also thing that the way FMS and client are chatting can be improved : let's say that FM Client request a given record with 20 fields, it seems to me that the client will issue 20 requests, wich will cause 40 packets of data to be exchanged (20 to ask for them 20 for getting them). I'd suggest that the client would ask the server : give me the fileds I want, 1 request, FMS will compute whats needed (maybe using my optimation tree), and make a big chunk of data and push it to the client. 2 exchange of a big data chunk.

Maybe I'm wrong  and it's already the case for each records, but at least it seems that if FM Clienst ask for 30 records, theres will be 30 request nowadays, while there' could be one : please FMS give me record 1,2,8,9,456…470 and then the server would compute the data that ,need to be send, it will divide it but the chunk size and number given the connection type, and then send it to the client.

The idea is to lower the number of request and hence packets exchanged, because that's the request access time that kills the performance. For instance I've never hit the 2MB/s throughput between my high speed client and server on Gigabit LAN, that's because there's plenty of requests exchanged (I don't use container fields, only text/number and calc fileds, I gues one can hit it with big container fileds).

The need to lower the network packet exchanged is especially more inmportand with Wifi GO, because Wifi has much slower access time than LAN.

 

- Layout Rendering speed in Form view, but especially in list and table view : In 2012, all the drwing should be instant, scrolling should be smooth as butter. I'm just talking about 100% indexed flat data, just considering the drawing part. To those who think a filemaker layout is complex, I say no, much much less than most webpages (remeber i'm talking about indexed flat data), we are in 2012, my 2010 iPhone 4's safari blows FMP 12 layout speed rendering. So clearly there's huge optimization to be done, especialy in list and table view, where even with latest improvments it hinder usage.

Maybe the Display Rendering could be done in a second thread for another core, nowadays most computers have more than one core, right now they seit iddle with Filemaker

 

Modified 2012-09-26 : Added last line about layout rendering by second cpu core

Outcomes