12 Replies Latest reply on Apr 10, 2012 1:55 PM by LSNOVER

    FileMaker and ESS Performance

    GordonShewach

      I've created a FileMaker database that's connected to an Oracle database via Actual Technologies ODBC driver. Everything works. It's just painfully slow doing very mundane things. Going from record to record takes about 5 seconds on a layout that has a few native FileMaker fields and about 25 Oracle fields displayed via ESS. Nothing fancy, except a portal containing 3 rows and 4 text fields (can you really call a portal fancy). No performance-killing calcs or summary fields.

       

      I've done work with ODBC and ESS before and the performance has been fine. Is this aberrant? Could it simply be that the database I'm accessing is large (student database for a 40,000-student university)? My FMP db only has 600 student records, but ESS connects it to the full student database in Oracle. The pipe to Oracle is a WAN, but it's a large university's WAN meaning really big pipes connecting units. I'm told it's a 1gig pipe and I've been able to clock speeds over 80Mbps during business hours.

       

      If I can't find a way to improve performance, I'll write some routines that bring the data in natively every night. I'd just like to avoid this if possible. Thanks for any insights you can offer from your own experiences.

       

      Gordon Shewach

      Desktop Services

      Ann Arbor, MI

        • 1. Re: FileMaker and ESS Performance
          jormond

          There are a ton of variables that affect that performance.

           

          How many fields are in the table?  Even though you are only showing 25 fields, FM has to download the entire record.  Specifically it downloads every record it puts its fingers into ( including records touched by a calc, variable, etc ).

           

          How many records in your found set?  That has a big impact on performance.  If possible, have your scripts go to find mode before you change layouts...then no data is moved, except the structure.  Then do your find.  It should help when you are dealing with a larger data set.

          1 of 1 people found this helpful
          • 2. Re: FileMaker and ESS Performance
            techt

            Do you have a filter on your portal? I've seen this kill performance in other solutions.


            Joshua has a point. Maybe they can give you second view into the records that you want to see. Have Oracle limit the data set before you work with it.

             

            Since you've had others that don't display this behavior, then I'd say it's something else.What happens if you're looking at the records directly, rather than through a portal?

             

            HTH,

             

            TT

            1 of 1 people found this helpful
            • 3. Re: FileMaker and ESS Performance
              GordonShewach

              Thanks for your thoughts, Joshua.

               

              I'm pulling data from 8 different Oracle tables with # of fields ranging from 6 to 40. I did play around with found set size and got similar performance with all 600 records as with 10.

               

              I did find a couple things that might be clues: 1) the portal did some tunneling (tblA-tblB-tblC) and removing the portal cut the flipping-through-records speed in half. Putting a different portal (without tunneling) in its place did not yield a speed penalty. 2) my initial speed test of the internet pipe was at 4:30p and I tested again at 6:00 p and found the speed had almost doubled from about 80Mbps to 140+. Similarly, the record flipping that took about 5 seconds is now down to about 2.5 seconds.

               

              So I glean from this that certain structural elements can impede performance and connection speed to Oracle on the WAN is an issue. I can't do anything about the latter, but I can play around with the FMP structure to isolate bottlenecks and either find alternatives or do the middle-of-the-night-import-data-natively routines just for selected data, not all of it.

               

              Gordon Shewach

              • 4. Re: FileMaker and ESS Performance
                GordonShewach

                I don't have a filter on the portal, but based on my response to Joshua above, I'm going to be on the lookout for structural bottlenecks and I think filtered portals and sorted portals could affect performance.

                 

                Limiting the data set would be great, but probably not an option. Too large a university to consider a request from one 600 student department.

                 

                Thanks, techt.

                 

                Gordon Shewach

                • 5. Re: FileMaker and ESS Performance
                  Malcolm

                  Limiting the data set would be great, but probably not an option. Too large a university to consider a request from one 600 student department.

                   

                  Put it the other way around. Too large to play fast and loose with confidential data. You shouldn't have access to the records that don't belong to you. Security/Privacy nightmare. You shouldn't have to pressure them to doing the right thing in the first place, etc, etc.

                   

                  malcolm

                  • 6. Re: FileMaker and ESS Performance
                    LSNOVER

                    Hi Gordon:

                     

                    As has been pointed out, Filemaker does not recommend FM Pro as a full tilt SQL database interface.  That said you can get good results, but you will have to be creative and design things very carefully.

                     

                    First rule is to limit the number of records that you get in any given found set.  You do not want to land on a layout that is showing all the records from a large SQL database, or things will get sluggish real fast.  As long as you keep the found set to a reasonable size, I've found performance to be acceptable for most tasks.  You do not need to limit the overall data in the table, but you need to make sure that some filtering criteria is applied to keep the amount of data Filemaker is working with at any one point, in the range of a couple thousand records or less.  I've found creating summary views in SQL to be very helpful along these lines.

                     

                    Portals can be a big problem, especially if you are doing sorts or the related record set is large.  Avoid sorts where you can.  Filemaker has to read in all the related data and do the sort in it's own memory. Since it does not have any means of using the SQL indexes or creating it's own, these sorts can be very painful.  I've had some cases where I had to sort the portal and because the data was relatively large, the performance was just unacceptable.  So I've constructed some ASP and RealBasic "mini-apps" that I use for showing related data through the Web Viewer object.  It's more work for sure, but it provides a hybrid solution that performs well.  I hope in some future version of Filemaker we will be able to allow the sorting to be done by the server. 

                     

                    If you can share a bit more of what your app. needs to do, perhaps we can offer some additional suggestions.

                     

                    Regards,

                    Lee Snover

                    • 7. Re: FileMaker and ESS Performance
                      GordonShewach

                      Appreciate the thoughts, Lee.

                       

                      After additional experimentation, there were simply too many problems for me to maintain the FIleMaker interface to SQL. I thought the couple dozen fields I was accessing that way was reasonable, but in addition to performance issues (again, I had performance issues on a 10-record found set) I ran into issues such as: finds on ESS data (indexed!) not working, finds needing to be entered case sensitive, finds requiring all not partial matches, et al.

                       

                      So I'm bringing the data in natively. Most of the data change infrequently or never (name, address, gender, GPA, credit hours, major, minor, etc.) so my script to bring in the data can run less frequently than daily. I use auto-enter + relookup field contents for fields in the parent table and loops to populate the child tables.

                       

                      I think the bottom line, since others and I have not had this problem on other FileMaker/ESS structures, is that the databases accessed were simply too massive to work well this way.

                       

                      Gordon Shewach

                      • 8. Re: FileMaker and ESS Performance
                        thewoz

                        I routinely connect my FileMaker tables to large SQL tables through ESS and do not generally have these kinds of performance problems. In some instances, I access multiple tables with several hundred thousand records each on a single layout with records from 10 or more related tables shown on a single layout, some in portals. I even have clients in remote offices connecting to my solutions via VPN connections with very little speed degradation. However, good ESS performance requires several good design practices.

                         

                        The most important practice is to know the structure of the SQL database that you are connecting to. What are the primary and foreign keys in each table, which fields are indexed, and what are the native relationships between the SQL tables. It's best to get a relationship graph and field-definition tables from your SQL database administor to make sure that you understand the field definitions and how the SQL are designed to be linked together for optimal performance. If you link the tables in other ways in your FM relationships window, performance is likely to be highly degraded.

                         

                        The most important practice is to only use primary keys in the SQL tables when you set up relates between SQL tables in the FM relationships window. Similarly, you need to use an appropriate key for relating FM tables that you have created to SQL tables. For example, if you are tracking information about students in your FM table, you need to have a field in your FM table with the same id that uniquely identifies the student in the SQL tables.  You need to explicitly define this in FM as a unique, non-empty, prefereably numeric, indexed field. Then use this as the field which relates your table to the proper SQL table. Usually this is a unique integer that is automatically assigned when new records are added to the SQL table. You do not want to set up a relationship based on a text field, such as a student's last name, especially if this field is not indexed in the SQL table since this will significantly affect performance. If you choose the "Allow creation of records in this table via this relationship" option in the "Edit Relationship" dialog box, this ID will automatically be created in your FM table if your data entry layout is focused on the proper SQL table. If these relationships are set up properly, you should see good performance even when searching for partial text matches in unindexed text fields.

                         

                        The other key practice when using ESS, as mentioned by others in this thread, is to avoid filtered or sorted relates on records in the SQL tables.

                         

                        Good luck.

                        Karl

                        • 9. Re: FileMaker and ESS Performance
                          GordonShewach

                          Thanks for your detailed response, Karl.

                           

                          Yes, I have charts of primary and foreign keys, relationships, and indices. Most of the relationships are simple StudentID to StudentID with the FileMaker-native StudentID field exactly as you indicated above.

                           

                          Based on your good experience with ESS and large databases, I just set up a test db with a single field (StudentID) with relationship to a single ESS table. I brought in 600 student records and started doing finds on the ESS fields for First Name and Last Name. And I got good performance. Not quite as good as finds on native FileMaker fields/data, but certainly acceptable. So that means that somewhere between this very basic test db and the real one I set up that had significant performance problems, there is some troubleshooting I can do to see what thing(s) I introduced that tanked performance.

                           

                          But even in my test db, I found that finds were case sensitive. If I did a find for last name "smith", the find would fail. But it would succeed when I did a find for "Smith". Or "Smi". Certainly I can script finds to put in appropriate case, but this is A LOT of work to script a find for every field. And you couldn't easily script for every scenario (McDonald). A requirement for case-sensitive finds is a dealbreaker for me. Any thoughts on this?

                           

                          Thanks, again.

                           

                          Gordon Shewach

                          Desktop Services

                          Ann Arbor, MI

                          • 10. Re: FileMaker and ESS Performance
                            LSNOVER

                            Gordon:

                             

                            Yes, SQL Database searches are generally case sensitive.  You can adjust this in SOME SQL Databases, but it's often difficult.  You can create alternate text fields for searching that store all upper case versions of the text, or you can script the Find to attempt lookups using Lower case, and Capital case.  It's not perfect, but it sometimes does the trick. 

                             

                            I've had my best results by building Hybrid databases where I keep copies of the Keys in Filemaker format.  Remember that Filemaker does not recommend using Filemaker Pro as a full blown SQL database front end.  I hope with future versions we may get some more control over what we can do between Filemaker and SQL in ESS.  The enhancements in version 12 are very helpfull, but there are many things that could be done to dramatically improve ESS in the future.  The key is making sure you let Filemaker know your ideas/concerns and that you are using this feature.

                             

                            Regards,

                            Lee

                            • 11. Re: FileMaker and ESS Performance
                              GordonShewach

                              Thanks, Lee. I didn't know that about SQL dbs generally requiring case-sensitive seaches. This coupled with the idea that FMI doesn't recommend using FMP as a front end to SQL (mentioned earlier in this thread also) tells me I made the right decision to script bringing in the data natively. The tricks I'd need to employ to perform finds in what will be dozens or hundreds of fields is a dealbreaker for me. Especially because much of the data I'm bringing in doesn't change much.

                               

                              Gordon Shewach

                              • 12. Re: FileMaker and ESS Performance
                                LSNOVER

                                Good luck with your project Gordon.  A hybrid approach may work well for you.