Preventing constant running of lookup queries

[ATroyer]

I have quite a few forms with lookup queries which are running constantly on other tabs of the same form(DO oriented) and slow things down tremendously. This happens whenever I insert/update/delete a document on another tab, even firing on an initial validation as shown in the picture. I have been scouring the forum and have yet to come across a reasonably simple solution. These are regular lookup queries, but they do contain subqueries as shown, which I am sure adds to the server communication load. However, the lookup is only used when a new document is inserted, after which it is useless. Is there any way to prevent this?
I'd rather not have to set up classes based on IMDB tables for the lookups, as it seems like a very complicated solution.

[ljwilson]

Thoughts:

1. Recode the lookup into a SQL Server view using join(s) instead of subquery, and import that view into your inde DB and use it for the lookup.
2. Rather than have the "blue" field linked to it, call the lookup with a button.
3. There might be a way to get the lookup to only search for one record if inserting, otherwise work as normal. Might have to work in conjunction with 2.
   above.
4. Do you have autolookup enabled for the blue field? Not sure if it would make a difference, but worth a shot.

...jack

[t.simoncini]

If you define your lookup queries to query a DO object instead a DB table, the framework uses a "DO lookup cache" that avoid multiple DB queries for the same object. It has its own downsides (i.e. you not always get the last data written on DB by other users), but it lightens the DB workload.

Best solution (if you only care about DB-related performance) is to use DB views, and possibly divide view panels (based on complete DB views with no lookups in INDE) and edit panels, with all the lookups you need.

[ATroyer]

I had a customer needed a fix quickly, so I did a quick and dirty work-around. I set the panels in each tab to default to form view and switch to list view when the corresponding tab is selected. In form view, the lookup is done on only one record, instead of the entire recordset, so I gained quite a bit of speed there. Doesn't take care of things long-term, but works for the moment.

[ATroyer]

I finally found a better long-term fix: I use classes to serve as value source lookups. On login, a number of IMDB tables are loaded with information relevant to the current user. These tables are then used to load the recordset of these classes during the On Get Value Source Event. This event is only called when inserting or editing a specific record, rather than during the loading of many records in a form. I am attaching two screen shots as one example of many. As an added bonus, a value list populated using this method is not limited to 20 or 30 items, but can be extremely long. Smart lookup works well with this type of list; the user starts typing and the search is practically instantaneous.
An added bonus to the preloaded IMDB tables is the much faster performing of various db lookups in On End Transaction, After Save, and other events that use db tables for processing.
One thing to be aware of is that the IMDB tables have to be refreshed or reloaded whenever there is a change of relevant data on the server, such as a new student, a new class, etc.

[ATroyer]

Note: I still use subqueries, but only when loading the IMDB tables; you cannot use subqueries when querying against IMDB tables, nor can you use union queries.

[ljwilson]

Allen,

Interesting technique--thanks for sharing!

I wonder if you could skip the IMDB tables and just have the classes populate directly from the database. There is thread on the Italian forum where the InDe user got rid of all of his IMDB tables and replaced them with classes.

Question about IMDB optimization
https://forum.instantdeveloper.com/viewtopic.php?f=5&t=89480

...jack

[ATroyer]

I decided to do an experiment by loading the same recordset using IMDB tables and Db tables and commenting/uncommenting as needed.

Screenshot 2020-12-12 103453.jpg (75.19 KiB) Viewed 222 times

Results will obviously vary depending on connection speeds, but here are the results loading from IMDB tables (2 milliseconds):

from IMDB.jpg (42.52 KiB) Viewed 222 times

and from Db tables (107 milliseconds):

from db.jpg (25.49 KiB) Viewed 222 times

The difference is not as great as I thought it might be, as the one required a trip to the server and the other did not.
One thing I noticed working with IMDB tables is that joins should be kept to a minimum when querying them, or you lose all the advantages of having data stored locally. I load the IMDB tables using the majority of my joins there.
I had a problem following theguru's thinking in the linked post, since I am just now learning some of the more involved aspects of programming. I will definitely keep my mind open to learning more about collections, etc