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

    Large SQL file


      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

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


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


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



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

            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

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

              • 4. Re: Large SQL file

                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


                  you might be looking for FETCH FIRST or OFFSET.


                  • 6. Re: Large SQL file

                    +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.



                    • 7. Re: Large SQL file

                      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).