Improving the performance of the doclinks query in process automation engine products
barbara.ballard 060000G9E4 Comments (7) Visits (7285)
Have you been running the performance dashboard to identify performance issues with your process automation engine product applications? Did you notice some long running queries that start with select * from doclinks? Or maybe you identified them in some of your database snapshots? In this blog entry I will explain how to improve the performance of this query.
This a typical doclinks query:
select * from doclinks where (ownertable = 'INCIDENT' and ownerid
= 12376 ) or ( ownertable='SR' and ownerid in (select ticketuid from sr where
ticketid= '' and class= '' ) ) or ( owne
The doclinks query runs whenever an application is opened or a new record is inserted into an application to determine if there are any documents attached to the record. This query turns on the paperclip icon in the application to indicate there are attached documents, as shown in the following screen capture:
This query can get quite complex if there are many associated records since the paperclip is turned on when there are attachments to other related records as well. For instance, if you have assigned an asset to your incident, then any attachments to the asset are also available to be viewed from the incident document link.
While it is always possible to turn off the doclinks query
by using the system property enab
Another solution is to simplify the query. All the doclinks queries can be found by querying the maxrelationship table searching for all relationships with the name doclinks.
* FROM MAXI
The parent field gives you the name of the table from which relationship originates and is usually the main table under the application.
Let’s take a look at a typical doclinks query. This is the whereclause from the doclinks query that is run from the incident application.
= 'INCIDENT' and ownerid = :ticketuid) or ( ownertable='SR' and ownerid in
(select ticketuid from sr where tick
The first thing to do is identify all the associated tables. This can be done by taking a look at all the subqueries. So if we separate the query into its subcomponents we will see the following:
(ownertable = 'INCIDENT' and ownerid = :ticketuid) or
ownertable='SR' and ownerid in (select ticketuid from sr where
(ownertable='ASSET' and ownerid in (select
assetuid from asset where asse
The ownertable identifies the parent table to which the document is attached. For example we see ownertable = ‘SOLUTION’; this brings all the documents attached to the solution associated with this record to the paperclip on incident. So for incident all the attached documents would come from incident, SR, Problem, wochange, worelease, solution, asset, locations, woactivity, jobplan and commlog records associated to this particular incident.
The next step is to work with the user community and identify all the associated documents which are not required to be viewed from the incident record. For instance if solution is not being used in your implementation, then it would not be necessary to see the documents attached to solutions.
Now to simplify the relationship,
just remove the section that is not needed. For example to remove the link to
the solutions documents, just remove the section shown in bold text from the whereclause in the maxrelationship table
shown in bold text from the whereclause in the maxrelationship table
Make sure you remove the "or", otherwise the query would not be syntactically correct.
This can be repeated for all the doclinks queries. If you are able to significantly simplify this query, it should improve your performance whenever you open a record.