Apex Code Development You need to sign in to do that
Don't have an account?
Michael Brumley Query questions
I'm writing what should be a simple Apex class to determine if a Lead being added already exists as an Account. To do this, I need to check for matching email addresses between the Lead and the Account Contacts, and matching company names and zip codes between the Account and the Lead. They have very little data in their sandbox, so I tested the best I could in my dev account, and had 92% coverage on the class with no errors. Right after deployment though, two of the queries started exceeding governor limits, in ways that just don't make sense to me. (I'm still waiting on specific data to recreate.) Here's the first, which generates a "Too many query rows:1001" exception.
if (TestLead.Email != null) { // Nested query returns accounts having contacts with matching emails Account[] emailAccts = [SELECT AccountNumber, Account_Activity__c FROM Account WHERE ID in (SELECT AccountID FROM Contact WHERE Email = :TestLead.Email)]; if (!emailAccts.isEmpty()) { bFoundExact = true; sExactType = emailAccts[0].Account_Activity__c; } }
The error column is 30, which is the beginning of the outer Select - does that mean it's the number of Account records that's too large? If so, is my problem related to the IN working on all the empty result sets the inner query will return? If that's not it, ...?
The second error is even more of a mystery, it's "Non-selective query against large object type (more than 100000 rows)." Here's the query:
for (Contact zipContact : [SELECT Account.Type, Email FROM Contact WHERE Account.BillingPostalCode = :TestLead.PostalCode]) { ContactDomain = DomainPart(zipContact.Email); if (ContactDomain == LeadDomain) // Partial match { bFoundPartial = true; sPartialType = zipContact.Account.Type; break; } }
Ok, so I get that the zip codes probably aren't indexed fields so it's got to check all the records, and they do have 50k accounts, but 1 million contacts still seems a bit unlikely. Any idea what I'm doing wrong? How do I read that message - that the result set has too many rows, or that the object being queried on a non-indexed field has too many?
Finally, I've searched high & low and can't find out how to tell which fields in an object are indexed, could someone kindly point me in the right direction?
Thanks,
Mike Brumley
After months of back and forth with support, we finally have a solution. There was never anything wrong with the query or the code, the problem turned out to be that SFDC was indexing the Contact email field for some users, but not for others. If you're getting this message, don't assume that even a standard field is being indexed just because it's supposed to be.
All Answers
Mike,
For the first error, you are getting too many rows for one list. The limit for a list is 1000. Use the query in a loop:
for(Account[] emailAccts = [SELECT AccountNumber, Account_Activity__c FROM Account WHERE ID in (SELECT AccountID FROM Contact WHERE Email = :TestLead.Email)]) {
//this will get you 200 records in a list at a time
}
For the second error, please check this thread:
http://forums.sforce.com/sforce/board/message?board.id=apex&message.id=2882
Mak,
Thank you for the response. Sorry, I should have mentioned that the entry point in this case is a trigger on the Lead insertion, so the absolute trigger limit of 1000 records would apply even in a loop.
That said, I still don't see how this could be the issue unless there's some fundamental about the way SOQL queries work that I'm not getting. First, it seems reasonable to assume that it's the outer query returning too many rows. The query will not be run for empty emails, and even if there were 1000 people using exactly the same email address, it doesn't seem likely that they would all be contacts in this company's data. Also, the docs say "There is no limit on the number of records matched in a subquery." which seems to rule that out.
So what seems to be happening is that a search for Accounts in which a Contact matches a single, specific, valid email address returns over 1000 Accounts. That just strikes me as very, very unlikely. Since all I need is the first match I can probably "fix" this with a Limit, but I'd much rather understand why I'm getting such an unexpectedly large number of rows.
For the second error, thanks for the pointer. I've actually already read that thread several times, which is why I'm trying to find out how to tell which fields are indexed. It's easy enough if it's a custom field, but like several of the people in that thread, I've had no luck finding out which existing fields are indexed. I misread the error message the first time - the limit is 100k rows, not a million, and there are 101k Contact records. So it's clear I need to filter somehow, but that's going to be tough to without knowing which fields I can use for that.
Thanks,
Mike Brumley
Simon,
Thanks - so that means, if a null is searched for, it matches all records? For breivity's sake I only included the immediate context, but there's additional code that only allows the queries to run if there's a valid search target. So it's not an issue in this case, but I would still like to understand this better.
I have to admit that part of the thread Mak pointed out really puzzles me - queries don't return nulls or deleted records, so why leave it up to the coder to filter them out of the search process? With all the work they put into efficient resource usage, wouldn't it make more sense to always ignore those unless they're requested? What am I missing here?
Mike Brumley
After months of back and forth with support, we finally have a solution. There was never anything wrong with the query or the code, the problem turned out to be that SFDC was indexing the Contact email field for some users, but not for others. If you're getting this message, don't assume that even a standard field is being indexed just because it's supposed to be.