Retrieving all sys_id's from large datasets quickly (cmdb_ci)

[emma3djames]

I am trying to query the cmdb_ci table to retrieve all of the sys_ids. This table currently has around 200K entries. I have tried GlideAggregate/GlideRecord and it takes on average 16-17s which is way too slow compared to a directly SQL query (400ms).

e.g. of code used

var table = new GlideRecord("cmdb_ci")
tableGR.query();

var ids = []
while (tableGR.next()) {
    ids.push(tableGR.sys_id.toString())
}

gs.info(ids)

Is there a better solution to this? I wish fetch an array of all the sys_ids in this table, similar to the SQL query below, rather than having to loop through abstracted data to retrieve this?

SELECT sys_id FROM cmdb_ci

[AshishKM]

Hi @emma3djames , 

What's business case to collect these sys_id(s) in arrayList, where are you using this information.

You can apply addQuery() on class to minimize per class count as we don't have access to direct database for SQL query.

-Thanks,

AshishKM

[Ashok Sasidhara]

Check for the business rationale and then see if there is a better solution to achieve that. If there is a valid business need to do this, then a workaround would be to do this separately for the various CI classes populated (E.g. cmdb_ci_computer, cmdb_ci_netgear etc.) instead of the entire CMDB.