The highest performance way to work with results in a List returned from Database.query()

databasedynamic-apexdynamic-soql

I have a custom object called Query_Definition__c that has two fields:

  • SObject_Name__c (text)
  • SOQL_Query__c (long text)

Within Apex, the requirement is to take the record details from the Query_Definition__c object and use them in a Database.query() call in a dynamic context.

SObject_Name__c will always have the proper name of a SObject within the system (e.x. Account, Custom_Object__c, Case, etc.).

SOQL_Query__c will always have a String value that represents a properly formatted SOQL query.

// Sample Query_Definition__c record

Query_Definition__c qd = new Query_Definition__c(
  SObject_Name__c = 'Lead';
  SOQL_Query__c = 'SELECT Id, Company, Status FROM Lead';
);

insert qd;

// Get the Query Definition record back and access it's field values

List<Query_Definition__c> qdList = [SELECT Id, SOBject_Name__c, SOQL_Query__c FROM Query_Definition__c LIMIT 1];

After getting the Query_Definition__c record back, I use the details to call a Database.query() method:

String qdSoql = String.escapeSingleQuotes(qdList[0].SOQL_Query__c);

List<SObject> dynamicQueryResultList = Database.query(qdSoql);

I need to then be able to iterate over the records and their fields within dynamicQueryResultList and perform DML operations within. What is the highest performance way of handling this situation?

This works but cannot access the fields in the result set:

// This works but cannot access field values in the rows
List<SObject> dynamicQueryResultList = Database.query(qdSoql);

for(integer i = 0; i <= dynamicQueryResultList.size(); i++) {
    System.debug(i);
}

// But this fails with an error code of, "Error: Variable does not exist: Company"
List<SObject> dynamicQueryResultList = Database.query(qdSoql);

for(integer i = 0; i <= dynamicQueryResultList.size(); i++) {
    System.debug(i.Company);
}

And even though I know the List contains only Lead records which would grant me access to the field values during iteration, this does not work for casting or list construction

SObjectType sObjType = ((Sobject) Type.forName(qdList[0].SOBject_Name__c).newInstance()).getSObjectType);
// above returns Lead successfully

// Returns error: Unexpected token '<'
List<sObjType> dynamicQueryResultList = Database.query(adSoql);
//

Help would be greatly appreciated!

Best Answer

First, arrays in Apex are zero-indexed.

Use:

for(integer i = 0; i < dynamicQueryResultList.size(); i++) {

To avoid an index out of bounds exception.

Second, almost all methods have similar performance characteristics. Don't micro-optimize for performance.

Third, you can access a field dynamically via the get method:

System.debug(i.get(Lead.Company));

Or:

System.debug(i.get('Company'));

Forth, you can't dynamically cast to an unknown type. You can construct a list type dynamically, such as:

Type.forName('System','List<'+qdList[0].SOBject_Name__c+'>').newInstance();

But this won't help you with the last lines of code, because data types must be resolved as compile-time, not dynamically at runtime.

You most likely want to use the Dynamic Apex documentation, read it carefully, it explains everything you need to know about what you're trying to accomplish.

Related Topic