[Implemented via Shortcut Resolve] FR - Narrow Context for Query

On this forum you can see a list of new features requested by users and you can also cast your own vote (you need to login to vote).
Post Reply
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

[Implemented via Shortcut Resolve] FR - Narrow Context for Query

Post by PointsWell »

If you have a query that interrogates BOG and the Member BOs of those BOGs have shortcuts and relationships and then you run the SQL log you'll see the query individually load all of those relationships.

When you do your load testing, you'll find that all of those BOs then create a MASSIVE overhead on the system and your system will slow to an absolute crawl.

Here's a video to demonstrate. There are two queries, one on the left is EXEC_SQL which creates a view the one on the right is a standard FIND ALL BOG query.

The video is 9 seconds long, let it run, it looks like it has stopped but it hasn't. The second query takes 6 seconds longer to complete than the EXEC_SQL one. All of the rules have been switched off for the BOG elements, the time lag is due to the way every record is being loaded into context.

This is obviously going to elicit a hard no from a client.

There needs to be a way to bring all of the records into context, without bringing anything that is not in required for the query. This will obviously have an impact on any processes that run on the records to bring that record back into context for processing, but that overhead is more easily managed than the initial experience of first loading the grid.

My initial plan was to use EXEC_SQL to bring the objects into a fake BO (i.e. one that exists only to pull this query) and then use the BO ID to find the real object for displaying the record, however it seems that objects that are retrieved in this manner are not real objects.

Details on the query.
BOG of 6 member BOs
3,500 instances
Several layers to child objects to each BO
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: [Implemented via Shortcut Resolve] FR - Narrow Context for Query

Post by PointsWell »

Here's the difference from Resolve Shortcuts being set to off for every shortcut

There are three queries:
  1. Default query on a BOG
  2. Changed Query to not resolve any shortcuts
  3. Control grid - made from using an EXEC_SQL to manually build the BOG into a virtual object
The button gets pressed at 3.21s, this triggers an operation to DISPLAY LAYOUT to show the three grids, the screen redraws at approx 3.53s and the third grid (the EXEC_SQL manual BOG query) shows the data at 4.41s.

The second grid (don't resolve shortcuts) then displays at 4.84s.

The first grid is the last to display at 9.41s, so manually switching off the shortcuts lops almost 5s from displaying the query.
Post Reply