Modeling Considerations

A big key to query performance is the data model that is used. It is much more efficient to build queries that use attributes rather than to build queries using fields that are not SQL convertible. Careful and intelligent use of attributes and triggers can limit the queries created. Certain queries, while looking simple and returning a very small subset, could actually cause extreme performance problems.

Below is a typical query, targeting objects of type Manufacturer, Facility, Group, and Project:

temp query bus Manufacturer,Facility,Group,Project * * where 
'from[Group Assignment].to.type == Person' select id dump ¿;

This query resulted in a small set of 12 objects, but it caused the generation of nearly four hundred select commands and excessive memory growth. A better solution would be to create an attribute that would make the query true or false. To create a SQL convertible query, first add an attribute (for example, “Assigned to Person”) to each of the four target types.

For the purposes of this example, the values of this attribute are “YES” and “NO”. When a business object of these particular types is created, set the value of attribute “Assigned to Person” to “NO”. Next, create triggers to modify the “Assigned to Person” attribute, either setting the value or unsetting it. As users update objects of the desired type(s), the assignedToPerson trigger checks the relationships of the object. The work the trigger will do can be summarized as “if the object meets the criteria specified in the original non-SQL convertible query, then modify the 'Assigned to Person' attribute to show that it does meet the criteria.” The trigger will check to see whether the business object has a relationship to a person from its group assignment. If it does, set the “Assigned to Person” attribute to indicate “YES”. If the user removes that relationship, set the attribute value to “NO”.

The new query would look something like this:

temp query bus Manufacturer,Facility,Group,Project * * where 
'attribute[Assigned to Person] == YES' select id dump ¿;

It is also important to include some consideration of query performance when designing your data model—in particular, when defining a new type.

For example, here are three modeling alternatives that minimize the need for queries on descriptions. All three can easily be maintained using the ModifyDescription trigger on the type:

  • Add an attribute Synopsis, which holds the first 100 characters of the description field.
  • Add an attribute Keywords, and require values to be specified at create time.
  • Write the description to a text file, check it into the object, and replace uses of description == by search[] ==.

In addition to the data model, knowing the application is another key. Really understanding how the application works enables users to create better queries. The end user knows what search parameters return lots of objects and which ones return a limited object collection.

Keep in mind that 3DSpace is best suited to expand/navigate operations. Queries are not the most efficient way to access the object-oriented data that the application manages. The best way to improve the performance of queries is to eliminate queries. A data model focused on the expand/navigation of relationships helps tremendously. However, one caveat concerning the use of type hierarchies and the use of !expandtype: Performance problems might occur when using 1000s of subtypes and then querying against the name of a base class. Traversing giant type trees can also cause problems. For more information, see Kindof Selectable for Types for a possible solution if large and deep type hierarchies are unavoidable.