AnsweredAssumed Answered

ODBC External Data and Performance

Question asked by raykennedy on Sep 23, 2015
Latest reply on Sep 24, 2015 by raykennedy

Just starting to utilize the ODBC accessing a MySQL table currently from my local server. For my testing I inputted about 10,000 records with about 26 fields for each record. Most of the fields are either integers, enum, timestamps or varchar with sizes maxed out at 35 length. There is only one field that holds up to 255 character so the data is relatively short and small just to give you some perspective.


I do load 1 image per record dynamically but I just do this through a webviewer with an external link to a remoter server (not on my local machine). So far this loads very quickly even in list view. The default find in FMP toolbar with multiple search queries seem to work very well and performs quickly so I am pleased with this so far. So far I have not added any additional calculation fields or summary fields.


My questions is for you more experienced users who have utilized external sources like MySQL where do you see a fall off on performance when the data loads into FMP and/or when you perform search's on the data within FMP. For example. I have a database that has roughly 80,000 records with about 320 fields per record with some larger text fields of 500 to 1000 characters. Most are varchar(5-35) and other integers, enum etc but there are at least 10 or 15 larger fields of varchar(60 to 255) and text fields. This particular MySQL table will be from a remote server on a shared hosting environment so not sure how that will affect performance, I imagine the import of the data will be slower but once it is in FMP my understanding is the source is meaningless.


I have seen some info on creating "views" to limit the import. I have to research that a little more but I am not sure if that is applicable to MySQL because it sounds like it is suppose to be on the Server side, FM side and I only saw examples with MS SQL.


Any input or best practices to consider or look at when developing an app with databases of this size. To some of you, this size might even be small. If so, is there a size larger that you see performance issues. I have another one that could easily grow much larger than 80,000 records pretty quickly but with far less fields/columns in the table than described above.