Performance-optimized access to Vertec objects

Apart from the Performance in the network and in Vertec Customizing , it is crucial that the performance is also monitored in folder expressions and in list settings – i.e. whenever Vertec data is queried via OCL .

Expressions and operations that run smoothly at the start and with small amounts of data can become performance eaters as more data is available in the system.

It is worthwhile to design the hits efficiently from the start and to understand and pay attention to the dos and don’ts described in this article.

And also point 6 of our 10-point plan for a future-proof Vertec installation : Low code customizing vs. overengineering .

Principle

Vertec loads the objects it needs to access from the database into the session memory. Once they are in the memory, access to them is fast. However, as soon as link objects, sublists, etc. of these objects are accessed, new objects must be loaded from the database.

The less (and less often) objects need to be loaded from the database into memory, the more efficient the process. This has to be kept in mind when filtering lists: loading many objects when only a handful of them are needed can often be avoided. Or, objects can be preloaded into memory in one go, which makes individual access faster.

It is important to understand the expression and what it does in order to be able to make high-performance decisions.

And if a more high-performance solution is not possible, you should know the reasons for this and also understand why a list or evaluation takes a long time (and label it appropriately by name, store it in a separate folder structure, run overnight , etc.).

How does OCL access work?

OCL evaluates expressions strictly from left to right and then uses the result of the first partial expression as the source for the next partial expression.

Simple expression such as projekt(or Projekt.allInstances) do not normally cause performance problems. If you extend this expression with a persistent member, e.g. “active” (projekt->select(aktiv)), it takes exactly the same amount of time, because the projects are already loaded with all members.

But if you select on a sublist such as projekt->select(phasen->size>0), i.e. all projects that have at least one phase, suddenly it becomes much slower. This is because the calculation is done from left to right as described above:

1. Load all projects.
2. Then for each project the expression phasen->size>0 This means that for each individual project in the list, the phases of that project are individually loaded from the database and then it is decided whether it will be included in the list or not.
3. Then the next project do the same, until the end of the list.

Vertec tries to load the data optimally for such (simple) expressions. If, as in the example above, it is clear that all phases of the projects in the list are needed, the execution of this expression loads the entire phase list into memory at once. This means that only a one-time database access takes place. However, for complex expressions or if sublists of sublists need to be loaded, this is not possible automatically and you have to pay attention to it yourself.

How does access via SQL work?

For queries via SQL (SQL folder, vtcapp.getwithsql(), service sums), the filters are applied on the database server and only the resulting objects are loaded into memory.

Simple queries such as “all objects of a certain class” are therefore as fast as OCL, but once you filter, SQL can be faster because the filtering on the database server is additionally optimized and not all objects have to be loaded.

If the objects are already loaded or pre-summoned , it is worthwhile to use OCL instead of SQL, because then only the memory, no longer the database, has to be accessed.

Access points

Larger amounts of Vertec data are accessed at these locations:

• In Office Reports : These access the data via Python Code , and thus you have all the options for high-performance access.
• In Vertec Lists : This can be set when loading the data, with the selection of the order type and the filter expressions.
• In Column Expression : As soon as calculations are made and sublists (and additional items) are shown, attention must be paid to the performance. Here the use of List Controllers and Custom Renderers is appropriate.
• In Scripts : As with the Office reports, you have all the options for high-performance access.

Vertec lists

For lists: The longer a list is (potentially), the more you have to be careful about how you build it or what column expressions you use in it.

Vertec lists are optimized for loading. Only the visible cells (plus another “page”) are calculated. Scrolling beyond this area will load the required additional objects, etc.

Therefore, sorting or exporting to Excel can suddenly take much longer than displaying the list itself, because in this case all objects have to be loaded.

Even if columns are summed, all objects must be loaded before the sum can be formed.

SQL folders

Sql folder are recommended if you want to select certain objects from a large amount of data based on a condition. Unlike the Expression folder, the data is selected by the database server, which in most cases is faster.

The disadvantage of SQL folders is that they do not automatically respond to changes in data and that the flexibility of OCL for queries is not available.

Expression folders

The advantage of Expression Folders is that the data is “live,” that is, it automatically updates when something changes to the data.

The disadvantage is that the data is not selected by the database server as in the case of the SQL folder, but all data is loaded and then selected.

Folders

For Folders , the objects are assigned directly to the folders and are shown unfiltered, so loading the objects is usually not performance relevant.

With this type of folder, you have to pay particular attention to the Column Expression , because they can load a lot of additional data and slow down the folder.

Be careful with Inclusive/Exclusive conditions in keyword folders: These conditions are performance-relevant because the data needs to be checked. In particular, Inclusive should be used with care, as it requires ALL the corresponding data in the system to be checked, which can take a very long time.

Link containers

Link containers are the “subfolders” created by either a Custom Link Type or a Wrapper Link Type . Once objects are linked, they appear in these lists.

The following principles are recommended:

• Show only link containers that are needed, i.e. should be reached via the user interface. In this case, not only Show containers , but also Always show containers – then he doesn’t have to check if it already has links for the display, which is faster.
• For Wrapper Link Types , pay attention to high-performance expressions.

Column expressions

No matter in which of the folder types or link containers mentioned here the data is located and how performant it is loaded – usually it is the non-performing column expressions that force a list to its knees.

If only persistent attributes (i.e. fields on the object itself) are shown, the list does not slow down after loading because the values are already loaded with the objects.

However, as soon as sublists or other objects are accessed or calculations are performed, attention has to be paid to performance. The expressions in the columns are processed row by row and load many different objects depending on them. Vertec tries to load very simple expressions optimally (see above), but often this is not possible automatically.

Therefore, the column expressions offer great optimization potential for high-performance lists. Often, the use of a List Controllers for the deployment of the data is worthwhile.

If you have a list that is already slow, you can use the Script: test folder performance to find out which column(s) you should optimize.

List controllers

For all folder types and link containers mentioned here, List Controllers can be used to preload objects or perform calculations.

The list controller has no influence on the loaded data, so it does not determine the list that is shown, nor can it improve its performance. It serves the performance in the columns and can do two things:

• Preloading : Load all data in one go to local memory for faster access in the list.
• Calculate and filter data, which can then be accessed in the columns via Custom Renderer .

Preloading

In scripts, Office reports and complex Column Expression in Vertec lists, it is worthwhile to load the required sublists into memory in one go. During processing, the system does not have to access the database every time, but can address the objects directly in memory, which is much faster.

This can be done via preloading the objects in a List Controller and of course also applies to all other scripts where data is loaded, e.g. in Office Reports .

The following options are available for preloading:

vtcapp.getwithsql()

The vtcapp.getwithsql() method allows you to load objects via SQL. The filter criteria are executed on the server and only the resulting objects are loaded.

The executing user must have administrator rights or the SQL Query right. For granting temporary administrator rights, the variant of extended permissions is available.

 # Preload der offenen Leistungen in Periode von einer Liste von Bearbeitern
bearbIdList = bearblist.idstring()    
whereclause = vtcapp.sqlwherebetweendate('datum', dateFrom, dateTo)
    
with vtcapp.SystemContext():
    leistList = vtcapp.getwithsql("OffeneLeistung", "bearbeiter IN ({}) AND {}".format(bearbIdList, whereclause), "")

vtcapp.fetchlinkmembers()

The vtcapp.fetchlinkmembers() method loads the sublist for a list of objects, i.e. the target objects, and at the same time makes the multilink current (which means that when the link is accessed, the list will not be reloaded). fetchlinkmembers() immediately prior to further processing.

# Loop durch die Phasen von Projekten, welche mindestens eine Phase "erteilt" haben
projekte = vtcapp.getwithsql("Projekt", "bold_id IN (SELECT projekt FROM projektphase WHERE status=1)", "")
vtcapp.fetchlinkmembers(projekte, "phasen")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

This method has huge optimization potential, see the following examples:

projekte = vtcapp.evalocl("projekt->select(phasen->select(status=1)->size>0)")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

projekte = vtcapp.getwithsql("Projekt", "bold_id IN (SELECT projekt FROM projektphase WHERE status=1)", "")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

projekte = vtcapp.getwithsql("Projekt", "bold_id IN (SELECT projekt FROM projektphase WHERE status=1)", "")
phasen = vtcapp.getwithsql("Projektphase", "projekt in (SELECT projekt FROM projektphase WHERE status=1)", "")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

SELECT BOLD_ID, BOLD_TYPE
FROM ProjektPhase
WHERE (Projekt in (1234567))

phasen = vtcapp.getwithsql("Projektphase", "projekt in (SELECT projekt FROM projektphase WHERE status=1)", "")
projekte = phasen.evalocl("projekt")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

projekte = vtcapp.getwithsql("Projekt", "bold_id IN (SELECT projekt FROM projektphase WHERE status=1)", "")
vtcapp.fetchlinkmembers(projekte, "phasen")
for projekt in projekte:
    x = projekt.evalocl("phasen->select(status=1)")

SELECT BOLD_ID, BOLD_TYPE
FROM ProjektPhase
WHERE (Projekt in (8454783,8662955,9775921,9779498,9782341,979...

Dos and Don’ts

->select on a long list of objects

Here it is worth considering whether a shorter list can be used to reach the same goal.

Example: There are many more projects in the system than project users. The expression

projekt->select(projektleiter.name = 'Christoph Keller')

therefore certainly takes longer than the expression

projektbearbeiter->select(name = 'Christoph Keller').eigprojekte

with the same result.

->select after a sublist of an object that needs to be loaded first

As soon as the attribute of a sublist has to be selected, the entire sublist must also be loaded, see the section How does ocl access work? .

Loading objects that depend on many objects is very expensive and should be avoided.

If this is unavoidable, Preloading should be used.

Or the corresponding information is provided on the object itself by means of or . As with the persistent attributes, the ->select unproblematic.

->select after a custom field on a long list

Custom fields are sublists and not “fields” on the item, as the name suggests.

Filtering for custom fields is very inefficient if the custom fields are not loaded yet, because they have to be loaded individually for each item.

For this reason, custom field filters are preferable to go over the custom fields. Instead of:

projekt->select(zusatzfeldbool('auswahl'))

So you write:

zusatzfeldklasse->select(boldid=1234567).zusatzfeldInstances
->select(wertBoolean).usereintrag->oclAsType(Projekt)

In our performance comparison with lots of data (see above), the 2nd expression was 180x faster!

It’s even faster with SQL:

bold_id IN (SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=1234567 AND wertboolean=1)

simply, the list is then not live, i.e. it does not change automatically when changes are made.

Or the corresponding information is written in Keys (Disambiguation) or Tags instead of in a custom field. As with the persistent attributes, the ->select unproblematic.

Display custom fields in a long list

Custom Fields are sublists, not persistent attributes. Therefore, they must be loaded row by row. For long lists, this can lead to performance degradation.

Use derived attributes instead of formulating OCL

There are various derived attributes in Vertec, especially sum attributes, which can bring a performance advantage over formulating the same query via OCL.

Example: On an activity assignment to a phase (taetigkeitphaselink), the time spent on it should be summed:

taetigkeitphaselink.evalocl("phasen.leistungen->select(typ.code='ADM').minutenInt->sum")

There is on the taetigkeitphaselink for this the sum attribute sumMinutenInt. If the attribute is used instead:

taetigkeitphaselink.sumMinutenInt

you get a response more than twice as fast.

->select after a derived attribute that needs to be calculated

One ->select after a derived attribute takes longer than a ->select to a persistent member, as it needs to be calculated.

In the vast majority of cases, however, this is faster than making the same call itself (see above). In addition, it will not be recalculated if the system has not changed.

Display derived attributes in a long list

Derived Attributes are computed at run time when displayed. This is done row by row, and the longer the list, the more time it takes.

It is better to show a derived attribute than to calculate the same value via OCL (see above), but it is worth considering whether the column can be dispensed with.

Use service sums instead of summing services

Loading and summing services takes a lot of time because there are usually a lot of services in the system.

To optimize this, Sums Of Services are available for accelerated summing and grouping in the form of OCL operators.

However, they should not be used directly in list columns because, as long as they are shown, they recalculate when any service in the system is changed.

A List Controller can be used for this purpose in list columns and can be accessed via Custom Renderer .

Sum columns

Lists in Vertec are loaded optimally: Only the visible objects and a “page” are loaded at a time. When scrolling, the required objects are reloaded step by step.

This only works if it has no summed columns, because in order to form a sum value, all objects have to be loaded.

Reorder Columns

Lists in Vertec are loaded optimally: Only the visible objects and a “page” are loaded at a time. When scrolling, the required objects are reloaded step by step.

However, if a list is re-sorted, all objects must be loaded at once. For long lists, re-sorting can take noticeably long.

Therefore, it is best to choose the default sorting so that it fits and does not have to be manually re-sorted.

->asset only if you have to

The Ocl list operator ->asset ensures that each object in the list only appears once. However, it is expensive because each object in the list has to be reviewed again, compared with all other objects in the list and at most sorted out.

That is why it should be ->asset use only if an object can be in the list several times at all. So do not use it in stock, but analyze the expression and use it only when necessary.

Business intelligence instead of evaluation lists

With the Business Intelligence (BI) module, all data can be evaluated in Vertec. The data is pre-calculated so that evaluations over longer periods of time and with large amounts of data can be shown efficiently.

A large number of measures are already supplied as standard or provided as Additional Features . Custom measures can also be added.

The BI thus offers a high-performance alternative to evaluation lists and can even show much more than a list, e.g. data grouped and in time series.