The effect of unbounded collections on system performance

Version 4

    Verified Product Versions

    Service Desk 7.6Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.x

    What is an unbounded collection?


    An unbounded collection is a collection on an object whose records can grow independently of the object the collection is on.  They can cause unnecessary data being loaded when you try to use a record on the object containing the collection.  In most cases the collection was created in error in the first place when only a one-to-one relationship was being created.  The following explains how this can happen:


    When you drag one object onto another within Object Designer you are doing this to achieve one of two things:


    1. Creating a one-to-one relationship between the objects, which you do by dragging Object 2 onto Object 1.  The result is a dropdown list of Object 2 records on your Object 1 window, ie. dragging a reference list object onto the Incident object.


    2. Creating a one-to-many relationship between the objects, which you do by dragging Object 1 onto Object 2.  The result is a collection of Object 2 on Object 1 which you can create records via a process action on Object 1, ie. dragging a new "Approvals" object onto the Incident object to create an "Add Approval" action.


    When you drag an object onto another you are prompted with the question: A relationship has been created to access 'Object 2' from 'Object 1'.  Do you also want to be able to access all related instances of 'Object 1' from 'Object 2'?


    If you are trying to create a one-to-one relationship you should say No to this question.  If you are creating a collection you should say Yes.


    In the example of dragging a reference list onto the Incident object you would say No because when using any item in the reference list you do not need to know about the other incidents using that item.  If you select Yes to the question asked when creating the relationship this would result in an Incident collection on the reference list object which we would call unbounded.


    What is the effect of an unbounded collection?


    Using the example from above if you create a relationship on Incident to a reference list and select Yes to the question in Object Designer this results in an Incident collection on the reference list object.  From this point on every time the system loads a reference list item, which will happen when you try to use the reference list item on a new incident, the data of every other incident that has ever used that reference list item is loaded from the server.  The data isn't used, but is loaded.


    For example take a reference list called "Incident Origin" with items "Phone", "Self Service" and "In Person" and log 10 incidents selecting "Phone".  The next time you log an incident if you select "Phone" from the Incident Origin dropdown the previous 10 incidents data will get loaded from the server.  You obviously have no need for that data and the client isn't displaying it anywhere but because the collection exists on the reference list object the data is loaded just in case.


    Over time the number of incidents getting loaded when you select "Phone" will only increase, and the Console will freeze while this happens.  This will eventually result in timeout errors or "Maximum request length exceeded" errors, which occur when more than 4MB of data is being loaded at once.  In either case the Console will be frozen for some time, the database under extra load and the server transferring megabytes upon megabytes of data.


    Another common example is if you drag the User object onto another object for use as a Creation User or Update User attribute and select Yes when prompted - this will create an unbounded collection on your User object. User records are the most commonly accessed data within Service Desk so the extra load of an unbounded collection here can quickly have a major impact on performance throughout the system..


    How to remove an unbounded collection


    Note:  The following should only be undertaken if you are experienced with Service Desk object design and are very familiar with your implementation.  If you are in any doubt please contact your local support provider for further advice.


    The attached SQL script (compatible with both SQL Server and Oracle) scans for potentially unbounded collection and reports back the results.  The key word is of course potentially, because there may be a legitimate reason for a collection to be unbounded.


    Any results of the script should be investigated thoroughly.  If they are not required, you can delete the collection from the object within Object Designer.  Before doing so please ensure the collection is not in use on any windows, queries or processes.  Do this on a test system first, and always have a full database backup available to roll back to.