8 Replies Latest reply on Aug 30, 2016 3:47 PM by David Moyer

    Large SQL file

    wade_knopf

      I'm creating a layout that has a portal to a large SQL database linked through ODBC. Problem is every time I open the layout or move it requerys the database which takes a large amount of time. Anyone have any ideas?

        • 1. Re: Large SQL file
          beverly

          1. change to form view (instead of list view), perform find to narrow set, change to list view if not too large

          or

          2. pre-query with Views (canned selects on the SQL server), so the set is already smaller

          or

          3. don't use FM as front-end to SQL backend if the dataset is that large

           

          beverly

          1 of 1 people found this helpful
          • 2. Re: Large SQL file
            fmpdude

            I would check with the ODBC vendor to see what your re-query options are. External DB access can be slow since there are lots of things going on that could slow you down.

             

            Some ideas...

             

            1. I would try to just grab a few records at a time and then fetch the next group if the user clicks "down-arrow" or some custom screen control.

             

            2. If that's still not fast enough, you might have to cache some of the remote database (that is save it in some table) so you get reasonable results.

             

            3. Note that portals can get VERY VERY slow with lots of data. I don't know what the magic number is, but I've had portals that were so slow I couldn't use them. In that case, I used the main form and a GTRR (Go To Related Record) to navigate back and forth.

             

            3a. I would try to take the portal out of the equation first to see if that's the problem.

             

            HOPE THIS HELPS.

            • 3. Re: Large SQL file
              wade_knopf

              How would you grab just a few records at a time?

              • 4. Re: Large SQL file
                fmpdude

                One method -- Using SQL you can SELECT records in groups with your WHERE clause. Using your Primary Key, you could keep track of the range of the "last set" retrieved in variables and have a script update those variables after each new fetch.

                • 5. Re: Large SQL file
                  David Moyer

                  Hi,

                  you might be looking for FETCH FIRST or OFFSET.

                  https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                  • 6. Re: Large SQL file
                    nicolai

                    +1 to beverly

                     

                    Views are the one I would personally would go for. You would need to create views on the SQL side. Make sure you allow views on your ESS setup.

                     

                    Nicolai

                    • 7. Re: Large SQL file
                      Mike_Mitchell

                      If you're using ESS (which is what it sounds like - using a portal and all) - then you'll need to rely on the techniques previously discussed, especially using a view on the SQL side. But you can't do anything about the auto-fetch function; it's how FileMaker's ESS feature works.

                       

                      If you're importing the data over ODBC into a FileMaker table, then you can constrain the query via SELECT (possibly augmented by FETCH FIRST and OFFSET). But it sounds like that's not what you're doing.

                      • 8. Re: Large SQL file
                        David Moyer

                        Ah, I should've said that I was using Import from ODBC; and that I was using SELECT TOP(n) ... (now I remember).