6 Replies Latest reply on Jun 13, 2013 11:36 AM by pmconaway

    Optimize Portal across a WAN with ExecuteSQL?

    WimLibby

      I have read many articles that describe how to speed up performance over a WAN, for example by not using summary fields or portals. So far, I can't figure out a way around using a portal in my solution, but wondered if using ExecuteSQL commands for the fields displayed in the portal would be a faster method then linking to other related tables. Any thoughts?

       

      Below is a portion of my relationship graph. On a layout for Shows, I have a portal showing all the Exhibtors for that Show (Show_to_Exhibtiors). The portal only shows a few fields, but they are all bascially from other related tables. For example, I show Status and Company Name, which I get from related tables - see the graph.

       

      My idea was to place new, unstored calculation fieldsd in Show_to_Exhibitors which are ExecutsSQL commands to get the Company name and Status. Then I don't need my portal to link to the other tables. I sort on the ExecuteSQL fields instead of on the field from the Show_to_Exhibitors_to_Company tables. If I understand FileMaker, this should speed it up because Filemkaer will not need to download all the data from the other tables, like each comapnies address, just to give me the company name. It woud just download the necesary records from Show_to_Exhibitors tables and what is necssary to complete the ExecuteSQL requests. It wouldn't have to also download records from multiple, related tables. Of course, this is all assuming the ExecuteSQL command is also not downloading all the other data as well - I could not find an answer to that question anywhere..

       

      So, before I spend the time to figure out ExecuteSQL, I wanted to know if this approach was worth the effort or not.....

       

      Capture.PNG

        • 2. Re: Optimize Portal across a WAN with ExecuteSQL?
          pmconaway

          Wim, an executesql statement will return everything you ask for that holds true for the query. So based on your example a query: "Select Companyname from show_to_event where Showid = x" will return just company name for the companies where the show ID = x (I didn't know what kind of value would be in this field), ExecuteSQL returns a delimited text list that you can use FMP functions to build a list to display. Whether this is going to be faster over a WAN. I'm not sure. There is a likely hood that it will, since you can define in your query just the fields that you need. I hope this helps give you more information about making a decision. Also the about SQL statement is just that, you will have to format it differently in the ExecuteSQL function.

           

          Paul

          • 3. Re: Optimize Portal across a WAN with ExecuteSQL?
            WimLibby

            This looks very interesting, but I am not sure how I could use it to speed up how portals views are generated.  I am not calling a "script" just going to a new layout with a portal.

             

            Am I missing something?

             

            Also, I am looking at using a Hosted FileMaker Serivce, like that offered by Productive Computing.  Do you know if if you can normally install these kinds of things on their server or not?

            • 4. Re: Optimize Portal across a WAN with ExecuteSQL?
              pmconaway

              Wim, How you you interact with the Portal in your solution?

              • 5. Re: Optimize Portal across a WAN with ExecuteSQL?
                WimLibby

                Below is a screen capture of my solution.  This layout is based on the Shows table.  The portal is located on the Exhibitor Tab and shows records from the Show_to_Exhibitors tables. 

                 

                When I select the tab, I get the window that shows it is sorting the 300or so records and it takes awhile.  I realize part of the portal data it is trying to pull down is from multiple tables attached to Show_to_Exhibtiors, so I am trying to minimize the overall amount of data that has to be pulled down using the ExecuteSQL step...if it is worth it.

                 

                If anyone has any other ideas for optimization, or a layou or table design change to speed things up, I am open to suggestions.

                 

                Capture1.png

                • 6. Re: Optimize Portal across a WAN with ExecuteSQL?
                  pmconaway

                  Ok, don't know if this will work but looking at your layout what about a combination of ExecuteSQL and Virtual Lists to make this interface go. Use ExecuteSQL to build the list (like what you planning on trying to do anyway) and the Virtual list to display the results. That way all you need to do is get the display data and the keys for the displayed data.And then your scripts to create the appropriate actions, which can be done out of site of the user. Just some thoughts.

                   

                  Paul

                  1 of 1 people found this helpful