3

I have a custom object called equipment___c with more than 100.000 records, and also related to product object. To get product codes that are in equipment object, i used the following query:

select product.productCode from Equipment__c

Here i'm getting 100000 records as a result.(checked this from dataloader). one productcode can be assigned to multiple equipments... So when i ran this query there will be repeated product codes. But i want distinct product codes.. so that i can display those codes in VF page.

when i'm using the above SOQL query in controller, i'm the getting the following error:

Too many rows 50001.

I also checked the following query:

select count_distinct(product.productcode) from equipment__c

I got the count as 689. so i want to print these 689 product codes in vf page.

I also tried GROUP BY clause. but it throws an error

Aggregate query does not support querymore(), use LIMIT to restrict the results to a single batch.

please help, how can i get distinct product codes..

  • If you're not gonna do any DML (and some other things) you can use the readonly attribute: salesforce.com/us/developer/docs/apexcode/… – rael_kid Oct 1 '14 at 11:08
  • Unfortunately im using DML operations in my controller @Lex – Ravali Pothineni Oct 1 '14 at 11:17
  • Can you elaborate on your data model and the information you're trying to query ? (by updating the question) Doing such queries sort of indicate you either have not optimized your data model, or you're trying to implement things in a non-optimal way. – Samuel De Rycke Oct 1 '14 at 12:11
1

Use a semi-join, e.g.

select productCode from productCodes where id in (select product from equipment__c)

0

I think there is only one solution.

Use readonly attribute in your VF page (page1)and fetch all productCode:

select product.productCode from Equipment__c

Then loop through all records and create list of unique productCode.

Then pass these productCode to next page(Page2) if user want to perform and DML operation.

On this (page2) you can skip readonly tag and perform DML operation and you already have list of unique productCode from page1.

If user do not want to perform any DML operation then he can view all productCode on page1 itself.

Note that if productCode size is very large like 100k or so then you may face governor limit related to heap size.

Otherwise it should just work fine for 689 records.

-2

When you need to show more than 1000 record on VF page or want to process on more then 50000 records, you must use AJAX toolkit of salesforce.

include connection.js into your page like below

<script src="/soap/ajax/32.0/connection.js" type="text/javascript"></script>

Now use following code snippet to understand and apply as your requirement

var result = sforce.connection.query("select name, id from account");
var queryMore = true;
while (queryMore) {
var records = result.getArray("records");
for (var i = 0; i < records.length; i++) {
//process records[i]
}
if (result.getBoolean("done")) {
queryMore = false;
} else {
result = sforce.connection.queryMore(result.queryLocator);
}
}

Please use this and let me know if you need any help.

Please mark this as an answer if this solve your problem :)

  • Getting huge data volumes asychronosly to the browser isn't really a solution. There' s gonna be a limit at which your browser runs out of memory. – Samuel De Rycke Oct 1 '14 at 12:13
  • In above example we are explicitly calling querymore request for subsequent 2000 records. So at a time you will get 2000 records in your browser memory which is not too much. You can remove while loop and can call after processing on first 2000 records for more records and can destroy previous records. This way will not going to full your browser memory. :) – Eric Oct 1 '14 at 12:24
  • 1
    If this is not a solution then can you please tell what is the solution for retrieving more then 100000 records as this is the question ? I really want to know the realistic solution ?? – Eric Oct 1 '14 at 14:44
  • 1
    sales_lion, Why solution is not realistic? Because information is processed via a browser, AJAX works best with small amounts of data, max 200 records. Ajax uses soap API, it takes time to process in construct/deconstruct of data received. Browser are inefficient in processing large amount of data (inefficient memory management). Your solution gives inappropriate use of toolkit which would require too much processing time, and browser rendering will be too slow. These may be the reason of downvotings. Pls consider help users, don't get demotivated just by voting only. @SamuelDeRycke is right. – Ashwani Oct 1 '14 at 15:45
  • 1
    Can anyone provide me realistic solution ?? In salesforce we don't have many things available and we do some workaround for that and that's what I provide as a solution . – Eric Oct 1 '14 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.