Using GlideAggregate as GlideRecord

[test54212131251]

Hi,

Just wanted to ask for your help/input regarding the usage of GlideAggregates in ServiceNow.

Currently we have built an application that converts each row entry of a table into Requested Items in the Service Catalog.

However we are facing performance issues since we have GlideRecord queries when converting the string value from the table into reference fields of the requested item.

We converted the GlideRecord queries into GlideAggregates and found an improvement in performance. However we are not using GlideAggregate for its purpose.

With this, is it allowed/documented to use GlideAggregates like how you use GlideRecord? Since GlideAggregate is an extension of the GlideRecord, can you perform all "GlideRecord" methods/propertiesfunctions using GlideAggregates?

To give you a background, converting 30 rows from the custom table using GlideRecord queries took us roughly 250 seconds...

When we changed the code to use GlideAggregate and converted 30 rows, it only took us around roughly 160 seconds for the transaction...

PS. Each GlideRecord in the script is expected to return only 1 value based from all the filters in the query

Sample GlideRecord query originally used:

var choiceRecord = new GlideRecord('u_choices');

choiceRecord.addQuery('u_form','Form Name');

choiceRecord.addQuery('u_choice_type', 'Choice Type Name');

choiceRecord.addQuery('u_description', 'Description Value');

choiceRecord.addQuery('active','true');

choiceRecord.query();

if(choiceRecord.next()){

cart.setVariable('variable_name', choiceRecord.sys_id);

}

Sample GlideAggregate query we used to try and improve performance:

var choiceAggregate= new GlideAggregate('u_choices');

choiceAggregate.addQuery('u_form','Form Name');

choiceAggregate.addQuery('u_choice_type', 'Choice Type Name');

choiceAggregate.addQuery('u_description', 'Description Value');

choiceAggregate.addQuery('active','true');

choiceAggregate.query();

if(choiceAggregate.next()){

cart.setVariable('variable_name', choiceAggregate.sys_id);

}

Note: Both codes are working but we are concerned since we didn't put .addAggregate for the second query and we are trying it as a glideRecord by dot-walking the sys_id

Any inputs/suggestions? Thanks!

[Jon Barnes]

I imagine it would work the way you are stating, however, I don't know why you would get any performance benefit from using GlideAggregate in this way over GlideRecord. Are you sure these results aren't anecdotal?

How many reference fields do you have that you are trying to convert for each row? and are those tables (the ones those reference fields point to) indexed on the display column? I don't think 250 or 160 seconds is reasonable for 30 records.

[test54212131251]

Hi,

We have over 6-7 reference fields. The results aren't anecdotal because we captured the response time in 2 different environments and both have shown improved performance. Our problem is we are concerned that GlideAggregates are not supposed to be used that way and might have a patch in the future that would break our code.

[Jon Barnes]

ok, in either case, I think that result time is really slow either way, so I would check the indexes on that tables that being referenced and ensure the column that is marked as Display column is also indexed. you can look at sys_db_object.list and find the tables to view the indexes.

[test54212131251]

We have actually applied indexes on the table but there are a lot more logic inside the code which makes the transaction longer.

The form/request is actually long so it's somewhat given that it is slow.