When troubleshooting slowness related to CMDB CI's, there may be instances where the queries are quite complicated do to the nature of the CMDB hierarchy.
Click this link for a picture of the CMDB Hierarchy
There have been cases where trying to return selections of configuration items results in queries that are overly complicated and slow.
When selecting from the hierarchy we can use tools like the INSTANCEOF operator within the platform to efficiently select from CMDB_CI using the class relationships between CI's to our advantage.
The INSTANCEOF operator handles all the logic of calculating the instances of a class. In other words, the INSTANCEOF operator will grab all items of the class you pass to it as well as the children of that class. When selecting from our table of configuration items there may be times when we want to grab all the items of a class or a large number of subclasses where it is more efficient to exclude 1 or 2 classes.
For example, if you want to return all computers in my CMDB, you may write a query that will return all items of the class cmdb_ci_computer but then you also need to grab the items cmdb_ci_win_server and cmdb_ci_server and cmdb_ci_unix_server ETC... This is why it is important to understand the hierarchy when structuring queries against the cmdb_ci table. If you understand the hierarchy you know that all the previously mentioned classes are children of cmdb_ci_computer.
Reference qualifiers are a common place to see this type of slow or complicated query.
Below is a short example but understand that as you add more and more classes to a qualifier, list view or glide record the queries generated can be impacted.
Having many OR clauses like the above can prevent the database from selecting a proper index or even using an index properly and the query will be slow so we need to improve this qualifier. If we understand the CMDB hierarchy, we know that the following items are all decedents of the cmdb_ci_computer class. Or to view it,
cmdb_ci_computer <--- PARENT
- cmdb_ci_server <--- CHILD
- cmdb_ci_win_server <--- CHILD
- cmdb_ci_unix_server <---CHILD
- cmdb_ci_linux_server <--- CHILD
Now we cannot use the sys_class_name cmdb_ci_computer only as we may actually have computers who use the sys_class_name 'cmdb_ci_server' and some that use 'cmdb_ci_win_server' this is where the INSTANCEOF operator is extremely useful. Taking the reference qualifier above we can modify it like so.
We have managed to remove 4 OR clauses to return the same results.
If you are using a list view to select items you can use the "Class 'is a' " to return the same results as the INSTANCEOF operator.
Viewing the Hierarchy or Locating Classes
If you are not sure how CI classes are related or need to locate what the sys_class_name would be you can navigate to the following within your instance.
Configuration -> CI CLass Manager and select Hierarchy in the top left.
The panel that appears allows you to see the CI hierarchy.
If you select a class, under basic info you can see the table name which is the class you can use in your code.