Cache all query results locally while in Design view for forms/reports to improve performance.
Sometimes queries can be expensive, or have errors affecting performance. Currently, if one of these expensive (i.e. very slow) queries is in a form's or control's Data source, (like a Combo Box, for example), that query can be re-evaluate repeatedly and frequently (very slowly EACH TIME) making any effort to edit or design the form a legitimate pain in the keester. Give us the option to cache these query results during development (Design View) so we can work FAST, like Access is supposed to!
Mark Burns
shared this idea
3 comments
-
Mark Burns
commented
Allow me to give you another example from very current personal experience:
GIVEN: You have an "expensive" ODBC Pass-thru query to a corporate data store that can easily run 20-30+ MINUTES _*each and every time*_ you try to run it (hundreds of millions of data rows, poorly indexed for your query purposes as is often possible in "Management reporting environment"-type corporate database scenarios).
Now, your task is to execute this query and report out the results. Simple, right?
So create a new query, and select the qryVeryExpensive from the table selection dialog under the Queries tab to past it into the query designer...
(wait that 20-30+ minutes here for the query to run - JUST so the query designer can read the resulting recordset's schema for the column/field names and data types...)
complete that process (eventually).
Then, select the "Make Table" option to change the query type and enter the target table name. oops - you misspelled it. Click on the SQL view to fix the target table name, and then click back to the designer view...
(NOW YOU AGAIN MUST wait that 20-30+ minutes here for the query to run - JUST so the query designer can read the resulting recordset's schema for the column/field names and data types...)
...get the picture here?
There are SO MANY paths to make little mistakes and mis-steps in Access like the above example that can cost you TONS of TIME - at the very least - for no real net gain!
When dealing with ODBC Pass-thru queries, ANY of the query/form/report designers MUST have a means of cache-ing a sufficient enough portion of the FIRST query's results so that subsequent "automatic" re-execution of the query DOES NOT NEED TO HAPPEN!
There needs to be an option to override the cached results and force it to run, if the circumstances dictate - UNDER THE USERS' CONTROL. -
Mark Burns
commented
@AlanC Yes, of course I have, but in some cases, say when outside forces change the underlying technical conditions under which the application runs and/or was first developed - like Admins moving a SQL Server to the far side of a WAN link post-deployment, for one possible example, there are serious impacts to being able to edit existing objects. It is for occasions like these that I make this request (in can literally mean the difference between being able to maintain an application and not).
-
Alan Cossey
commented
Have you tried leaving the query open when you are in Design mode. Sometimes that helps.