Apex Code Development You need to sign in to do that
Don't have an account?
S Kumar.ax1063 Non-selective query against large object type(While updating Account)
Hi,
I am getting "Non-selective query against large object type" warning whenever this query is run and try to update account.
Here is my code.
List<Account> acc =[select id,name,phone from account where name=:l.company and billingstreet=:l.street limit 1];
Account updateacc = new Account(id=acc[0].id);
updateacc.billingstreet = getStreet();
update updateacc;
l.comapany is lead company name.
l.street is lead billing street.
I have referred many blogs. It is mentioned that your query should be selective one and field should be indexed.
I asked to Salesforce support team to index on billing street in order to avoid this warning.
But according to support team this query is not meeting the criteria for adding the index and finally they denied to add an index on billingstreet.
I am not sure how to avoid this error.
Please help me !!!!!
Thanks,
Sunil
Alternativly (not as good), you could query all accounts with the same name, and filter out the right one (matching on billingstreet) in a for loop.
Hi,
Whenever an object has greater than 100K records any query on that object must be "selective". For a query to be selective it must have enough indexed filters (where clauses) so that less than 10% of the records (in our example 10K) are returned before applying the limit statement.
So, using for loop is not the better solution as already explained by "sdry".
I would suggest you to add some more filter in your where clause so that total number returned should be less than 10% of your total record before using limit.
This happens when your records in the object goes beyond 100K records which would have gone in your case too. Now in this scenario SF expects that your query should be using at least one filter criteria which should work out of index. Since youa re using Name and BillingStreet and BillingStreet does not qualifies for being an index, have you considered and requested for Name to be indexed. I believe your Account Name would really qualify for becoming an index.
Hope this helps.
I only suggested the for loop approach, because I wouldn't expect any organisation to have a high amount of account records all having the same name. If you have 100k accounts with the same name ... you'd have bigger problems than a mulfunctioning trigger if you ask me.
The following fields are indexed by default: primary keys (Id, Name and Owner fields), foreign keys (lookup or master-detail relationship fields), audit dates (such as LastModifiedDate), and custom fields marked as External ID or Unique.
So, the Name field is already indexed.
Hi Jitendra,
Thanks for your reply. But i can not add any other where condition as it is not meeting the business requirement.
Regards,
Sunil
Hi Kamal,
Thanks for your reply.As per my understanding Name field is already indexed by salesforce so i don't need to ask SF for indexing.
Thanks,
Sunil
you are right "Sdry", agree with you.
Hi Sunil,
Then only option is for you is For loop, however it may hit the Heap memory allocated in future if the count of record tends to increase. I would suggest you to talk with you Business analyst and come up with some more restriction.
Yeah Name is indexed too. I was not sure about that. So in that case why can't your query work, i will only expect this to fail if your combination of account name and billingstreet is resulting in more than 10% records means more than 10K records. Do you think this is the scenario?
Wow - this is an interesting question. After all - the Name field is indexed - so that should be sufficient to make this query selective.
Now, the following is a guess - but I think it's worth exploring....
It's unusual for there to be 100,000 accounts in an organizaiton - unless, of course, the organization is using person accounts. Person accounts are a strange beast, and with those - the account name is a concatonation of the contact first name and last name. I don't know for sure - but I'd bet that in the case of person accounts, the account name is not indexed.
If I'm right, the workaround to try would be to split your task into two queries:
1. Query first for the account name but filter on accounts where the RecordTypeID is not a person account record type. The RecordTypeID filter would be indexed (external lookup) and hopefully the name would be indexed as well in this case. So the query should be selective.
2. Query on contacts for a contact name that matches what you are looking for. Each person account has a backing contact record - its name should be indexed, thus the query should be selective. You can then query on account to find the contacts by querying on the PersonContactID field which also should be indexed (as an external lookup).
Again, I stress - this is just a theory. I don't have an easy way to reproduce your scenario so can't verify that this is what you are facing or that the solution would work. But if I'm right about the org using person accounts, I think it's worth exploring.
Please let me know what you find out - this is a very interesting problem.
Dan