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. 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
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.
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.
How would you grab just a few records at a time?
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.
you might be looking for FETCH FIRST or OFFSET.
+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.
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.
Ah, I should've said that I was using Import from ODBC; and that I was using SELECT TOP(n) ... (now I remember).
Retrieving data ...