4 Replies Latest reply on Sep 24, 2015 3:41 PM by raykennedy

    ODBC External Data and Performance

    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.

        • 1. Re: ODBC External Data and Performance
          mikebeargie

          So it sounds to me that you're using MySQL via ESS.

           

          Here's a thread that already has some good suggestions:

          FileMaker and ESS Performance

           

          you might want to google "filemaker ess performance" and see what comes up.

           

          What I've found is that it's usually the shadow calculations and summaries you can add on to an ESS table that slow things down the most.

          • 2. Re: ODBC External Data and Performance
            wimdecorte

            The FileMaker Training Series book has some good "things to watch out for" in the integration chapter.

            • 3. Re: ODBC External Data and Performance
              crw030

              Ray.

               

                We do not use MySQL but MS-SQL via ESS - and the biggest problem I see is with searching.  You really have to be careful allowing users to craft Find requests directly on very large SQL tables.

               

                I think the main "gotchas" are created when Filemaker tries to build a suitable query for your back-end source.  Please note if you are using ESS to store data - you are definitely using it differently than FM intended.

               

              You will also have to manage your own indexing, something that your Filemaker files might all be doing automatically.  That "automatic indexing" behavior will not occur on typical RDMS.

               

              I'm over a year using Filemaker ESS and users still just want to type text in without the "==" part.  Hence why you'll see some posts talking about scripting finds for users against ESS sources.

               

                Also recommended reading (I believe both of these are by Steve Lane of Soliant):

              1. techbrief_intro_ess.pdf (Introduction to External SQL Sources)  *overview
              2. best_practices_ess_fm13_en.pdf (FM13 ESS Adv Technical Brief)  *really great - highly recommend

              I had to request # 2 from Inside Sales, I think it is part of the Filemaker Developer Subscription, but should be read by anyone doing ESS.

              • 4. Re: ODBC External Data and Performance
                raykennedy

                Thanks everyone for the input  and suggested resources on this. I have a pretty good handle on MySQL database design but am very much a novice at FMP. The one advantage I have here is I can structure MySQL tables the way I would like and since this build is primarily for me and my business I really only have my own constraints to answer to.

                 

                What I will probably do in the mean time is put some scripts on the back end of this so I can create smaller tables for the purpose of dealing with filemaker interface until I get a better feel on what my performance limitations are. Still have a lot to learn on this subject. Your suggestions have been helpful and great starting point for me.


                Thanks again for the comments.