DB Performance (Magik)

This page contains performance hints accessing database information through Magik.

---

Inserting Data

Avoid db_set.add()

You do not want to use db_set.add() on 1:n or 0:n joins! This method is very very (extremely) slow. Always use the reverse join field to set the relationship. This approach is at least 50x faster!

Bulk Data Manipulation

If you have a large number of features you are adding, modifying, or deleting, you may want to use !notify_database_data_changes?! and view.notify_suppressed_data_changes(). See Main Help's Application Development->Application Development Techniques->Database change notification->2 Making data changes for more details. Improves performance by at least 50%.

Also the above may fix errors relating to "mevents.c:239:Error - events queue overflow"

Other Areas

RWO_TYPE

There are several ways to get the rwo_type from a geometry or a record.

record

You can get a handle to the rwo and do an rwo.rwo_type or you can do rwo.descriptor.name. The rwo_type call is nearly twice a slow as calling rwo.descriptor.name. So use rwo.descriptor.name, which also works for ds_records, for determining the record type.

geometry

For the geometry you can call geom.rwo.descriptor.name or you can use geom.rwo_type. In this case the geom.rwo_type is faster by double then the call to RWO's descriptor. So my guess is that the rwo_type on rwo actually looks for a geometry and does an rwo_type on a geometry.

Summary
On rwo_record or ds_record, you will want to use obj.descriptor.name. On a geometry use geom.rwo_type.

---

Indexes

If you do many queries on a collection that use something other then the key field, you should consider using indexes. Indexes allow you to identify a combination of fields that is searched more often then others. The indexes are stored in the database and is used to quickly find objects matching the criteria given. There are 2 ways to create indexes. The first is through the CASE tool. The CASE Object Editor has a button that allows you to create indexes. The second way will not require a data model change. you can use add_index() on the collection.

+ Additional Information

• If the database is modified to contain the indexes (i.e. from the magik prompt), then provided the changes are done in the case tool to exactly match those indexes no further activity will take place on them when the case is next applied. If the case changes are not made, or do not match then there is a risk that the indexes will get dropped and/or recreated differently during the apply. The apply works by comparing the state of the target dd with the current case architecture, so if they match there will be no work to do.
• Be careful about making changes to datamodel lower down the alternative tree and posting up. This is a route that can lead to duplicate indexes on a table if the index is applied at 2 levels, resulting in 2 duplicate index definitions once the alternative streams are merged. We have this happen at other sites and it has caused problems later on with wasted space, duplication of activity and also tracebacks during ds_transfer on older core versions. We recommend that any test alternatives used for such activity are discarded, and the data model changes are re-implemented at top and then merged down.
• If you create an index on 2 fields then the first field in the index list can be used to speed up searching, but not the second. However, the optimization routines for searching may not be able to identify the usefulness of the index to the search (usually they do), and/or the complexity of the search definition may mean that a speedup that would be obvious from fuzzy-logic and a specific use of data would not apply in the general case and so optimization has to ignore the index. So the presence of a partial index to help speed up a search should (and usually does) get used, but it's not guarantee that it will always happen.

---