1

I am attempting to pull data from SF and replicate it into a database for analytics. I have been running into a problem with the Event object. I included a subset of the desired query below, with a very low limit, to show an example.

curl -H 'Authorization: Bearer REDACTED' 'https://na6.salesforce.com/services/data/v37.0/query/?q=select+RecordTypeId,+WhoId,+WhatId,+Subject,+Location,+ActivityDateTime,+ActivityDate,+StartDateTime,+EndDateTime,+Description,+OwnerId,+Type,+CreatedById,+LastModifiedById,+SystemModstamp+from+Event+where+SystemModstamp+>=+2015-06-09T00:00:00Z+order+by+SystemModstamp+ASC+LIMIT+10' | json_pp % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 88 0 88 0 0 0 0 --:--:-- 0:02:04 --:--:-- 20 [ { "message" : "Your query request was running for too long.", "errorCode" : "QUERY_TIMEOUT" } ]

Running explain on the fully desired query produces the following.

```
{
   "plans" : [
      {
         "sobjectCardinality" : 796213,
         "notes" : [
            {
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsDeleted"
               ],
               "tableEnumOrId" : "Event"
            },
            {
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsArchived"
               ],
               "tableEnumOrId" : "Event"
            },
            {
               "fields" : [
                  "IsTask"
               ],
               "description" : "Not considering filter for optimization because unindexed",
               "tableEnumOrId" : "Activity"
            }
         ],
         "fields" : [
            "SystemModstamp"
         ],
         "leadingOperationType" : "Index",
         "relativeCost" : 0.216667483034208,
         "sobjectType" : "Event",
         "cardinality" : 35830
      },
      {
         "notes" : [
            {
               "tableEnumOrId" : "Event",
               "fields" : [
                  "IsDeleted"
               ],
               "description" : "Not considering filter for optimization because unindexed"
            },
            {
               "tableEnumOrId" : "Event",
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsArchived"
               ]
            },
            {
               "fields" : [
                  "IsTask"
               ],
               "description" : "Not considering filter for optimization because unindexed",
               "tableEnumOrId" : "Activity"
            }
         ],
         "fields" : [],
         "sobjectCardinality" : 796213,
         "sobjectType" : "Event",
         "leadingOperationType" : "Other",
         "relativeCost" : 0.216667483034208,
         "cardinality" : 0
      },
      {
         "fields" : [],
         "notes" : [
            {
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsDeleted"
               ],
               "tableEnumOrId" : "Event"
            },
            {
               "tableEnumOrId" : "Event",
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsArchived"
               ]
            },
            {
               "tableEnumOrId" : "Activity",
               "description" : "Not considering filter for optimization because unindexed",
               "fields" : [
                  "IsTask"
               ]
            }
         ],
         "sobjectCardinality" : 796213,
         "sobjectType" : "Event",
         "leadingOperationType" : "TableScan",
         "relativeCost" : 0.700525615101544,
         "cardinality" : 12441
      }
   ],
   "sourceQuery" : "select RecordTypeId, WhoId, WhatId, Subject, Location, ActivityDateTime, ActivityDate, StartDateTime, EndDateTime, Description, OwnerId, Type, CreatedById, LastModifiedById, SystemModstamp, Five9__Five9ANI__c, Five9__Five9CallType__c, Five9__Five9Campaign__c, Five9__Five9HandleTime__c, Payments_Pitch__c, Asked_For_Merchant_Referral__c, Short_Comments__c, Activity_Case_Safe_Id__c, Created_Date_Time__c, Velocify_Action_Name__c, Contacted__c, DM_Access__c, Demod__c, Closed__c, Five9__Five9WrapTime__c, Did_you_Pitch_Upsells__c, Did_you_Close_the_Upsell__c, Why_didn_t_they_close__c, Call_Duration_In_Minutes__c, Signpost_Call_Task__c, Demo_Status__c, Last_Modified_Date_Time__c, ringdna__Abandoned_Call__c, ringdna__Call_Direction__c, ringdna__Call_Duration__c, cirrusadv__Created_by_Cirrus_Insight__c, cirrusadv__Email_Opened__c, cirrusadv__First_Opened__c, cirrusadv__Last_Opened__c, cirrusadv__Num_of_Opens__c, cirrusadv__Template_Name__c, cirrusadv__Time_to_Open__c, cirrusadv__isTracked__c, ringdna__Created_by_RingDNA__c, ringdna__Voicemail__c, Activity_Owner_Role_Name__c, Campaign_Spend__c, Notification_Hold__c, Notification_Scheduled__c, Timezone_Event_Date_Time__c, Timezone__c, Proactive_Outreach_Date_Number__c, Subscription_Upgrade__c, Velo__Action_Name__c, Velo__IsVelocifyAction__c from Event where SystemModstamp >= 2014-06-09T00:00:00Z order by SystemModstamp ASC"
}
```

Given that the explain show that the query has an index to use for SystemModStamp, I'm curious as the why we see timeouts. Furthermore, attempting to add a limit as low as 10 and reducing the number of fields does not allow the query to complete either.

Is there some sort of SF limiting going on here? I can't explain why we are seeing these results.

  • 2
    How many event records do you have in the system? – d_k Jul 1 '16 at 18:13
  • Open a case with support. – Adrian Larson Jul 1 '16 at 18:44
1

I agree with the user comment above, the answer depends on how many Event Records you have in the system. It is weird that limiting the query to 10 still times out though. Anywho, if you have record counts in the millions, here is some helpful info on optimizing your query https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

The most common quick fix is having Salesforce apply a custom index to the field you are using in your filter condition. But as per this article, the SystemModstamp field should be indexed by default. https://help.salesforce.com/apex/HTViewSolution?id=000006007&language=en_US

This may be something you will need to contact Salesforce support about. The situation seems fishy.

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.