function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
bssantoshbssantosh 

Querying large number of records

Hi,
 
I need to fetch 200+ Campaigns and the NumberOfLeads and NumberOfContacts associated with them satisfying a particular condition (like only those members to be counted with a particular status). I dont know if I can use a join to do this (as the Status info is in the Members table).
What would be the best and fast way of doing this? The Member count can be 100000+!
 
Thanks.
zachzach
There aren't joins (yet) & what I do is create an array of Id's from one table and use that array to retrieve the information from the other table.  For instance, I do a lot of case reporting and work with tens of thousands of cases.

What I do first is get the ownerId and contactId for the cases that fit my criteria.

Code:
sql = sforceClient.query("Select Id, ContactId from Case where (RecordTypeId = '0123000000003S5AAI' or RecordTypeId = '01230000000DHlnAAG')");

newArray = buildArray(sql);

function buildArray(array) {
 returnArray = new Array();
 var count = 0;
 if (array.size == 0) {
  return returnArray;
 }
 if (array.done == true) {
  var batchsize = array.size;
 } else {
  var batchsize = 200;
 }
 for (var a = 0; a < batchsize; a++) {
  returnArray[count] = new Array();
  returnArray[count].push(array.records[a].get("Id"),array.records[a].get("ContactId"));
  count++;
 }
 for (var a = 1; a < 10000; a++) {
  if (array.done == false) {
   array = sforceClient.queryMore(array.queryLocator);
   if (array.done == true) {
    var batchsize = array.size-(200*a);
   } else {
    var batchsize = 200;
   }
   for (var b = 0; b < batchsize; b++) {
    returnArray[count] = new Array();
    returnArray[count].push(array.records[b].get("Id"),array.records[b].get("ContactId"));
    count++;
   }
  } else { break; }
 }
 for (var a = 0; a < returnArray.length; a++) {
  if (returnArray[a] == "" || returnArray[a] == null) {
   returnArray.splice(returnArray[a],1);
  }
 }
 return returnArray;
}

 


I then push those Id's into an array (using querymore if the results are more than 200).  I then use that array to build my dataset of other stuff I need from other objects.  For instance, I'll use the ContactIds to get information from Contact, and then the AccountIds (from the Contact object) to get data from Account.  I'll also use CreatedById to retrieve information from the User object.

Seems like a pain (and it is) but it's the best way I've found to get large amounts of data from different objects without the ability to do joins.  If anyone else knows a better way, let me know.
michaelforcemichaelforce
 
A good function I use to get all of the results from multiple query / querymore calls into one array is "concat"...
 
Code:
AllRecords = AllRecords.concat(queryResult.records);