+ Start a Discussion
HGSHGS 

SOQL strange behavior, Taking too long to when ID filter is added

Greetings,

I am developing a VF page, and have a query that searches contact records based on some criteria, taking input from user on the page.

Here is the scenario:

  • Contact is the standard Contact Object
  • MyObject is a custom business object.
  • ConnectorObject is a custom object, which acts as a connector object between MyObject and Contact (M-M relationship between Contact and MyObject )
Here is the query that is executed as the final search query:

select Id, Name,Email,MobilePhone,Account.Name, ownerId  from Contact   where Contact_Status__c = 'Active' AND   id in (select contact__c from ConnectorObject__C where  MyOBject__r.Type__c = 'SomeValue')  AND (id NOT in (select contact__c from MyOBject__c where Type__c = 'SomeValue'))    order by Name limit 1000

The query takes around 0.8 seconds with around 1000 contacts, and around 200 MyObjects and around 400 Connector Objects.

Now, another requirement on this search is that only those contacts should be brought whose owner is the current logged-in user.

So, I added another condition to the query, to check ownerID and the query became (took Current User Id in variable and inserted into query):

select Id, Name,Email,MobilePhone,Account.Name, ownerId  from Contact   where Contact_Status__c = 'Active' AND   id in (select contact__c from ConnectorObject__C where  MyOBject__r.Type__c = 'SomeValue')  AND (id NOT in (select contact__c from MyOBject__c where Type__c = 'SomeValue'))  and ownerId = '0000xxxx0000xxxx00'   order by Name limit 1000


Now, when the query is executed, it is taking about 32 seconds !!.

Thinking that this might be because of so many conditions, I tried removing one condition : Contact_Status__c = 'Active'.  But the result is same, it is taking 32 seconds. I didnt try removing the 'Nested Conditions', as the search won't be possible without that.

I verified the query by running it in SF Explorer and noting the time lapsed.

So, the end result is, that as long as I dont put the OwnerID filter, it is fine, but as soon as I put OwnerID in the condition, the time increases exponentially, to 31-33 seconds !!

So, I ended up in removing the OwnerID from the filter..., and running a for loop after the query to eliminate the un-wanted records.., but this is not a good solution, as I might miss records because of the 1000 limit on the query !!

Has anyone else faced the same problem ? The time increasing manyfolds is a matter of concern, as probably I am doing something wrong, or there is a flaw in the SFDC query processor. Or, probably I am violating some hidden SOQL rule !!

Help Appreciated.






 

HGSHGS
Voila !!!
 
The problem is resolved now, seems like this was a bug !!
 
-------
HGS