Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Performance issues querying a specific table: EventWhoRelation

I've been getting errors using SF_Replicate on the object: EventWhoRelation:

Msg 0, Sev 16, State 1, Line 20 : --- Starting SF_Replicate for EventWhoRelation V3.3.4 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 26 : 16:54:05: Parameters: salesforce EventWhoRelation Version: V3.3.4 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 76 : 16:54:05: Drop EventWhoRelation_Previous if it exists. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 85 : 16:54:05: Create EventWhoRelation_Previous with new structure. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 110 : 16:54:06: Run the DBAmp.exe program. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 16:54:06: DBAmp Bulk Operations. V3.3.4 (c) Copyright 2006-2016 forceAmp.com LLC [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 16:54:06: Populating local table EventWhoRelation_Previous , GTOSFDBAMP01 / SalesforceBackup . [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 16:54:06: DBAmp is using the SQL Native Client. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 16:54:06: Opening SQL Server rowset [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 17:24:12: Error: RunQuery failed with com_error. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 166 : 17:24:12: QUERY_TIMEOUT: Your query request was running for too long. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 17:24:12: Error: DBAmp.exe was unsuccessful. [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 181 : 17:24:12: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe Export "EventWhoRelation_Previous" "GTOSFDBAMP01" "SalesforceBackup" "salesforce" [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 329 : --- Ending SF_Replicate. Operation FAILED. [SQLSTATE 01000]

Most of other objects work fine, and the large objects that get timeout make sense due to the amount of data in other objects. But EventWhoRelation has very few records (7500) compared to my larger objects (some over 200,000 that run similar queries fine), so I'm not sure why querying it is slow.

If I run a simple SQL query through DBAmp, it takes ~27 minutes:
Select TOP 100 Id From Salesforce...EventWhoRelation

But if I run a SOQL query through Workbench, it's fast (1-2 seconds):
SELECT Id FROM EventWhoRelation LIMIT 100

This is the only object I've noticed a big performance issue on. I've tried dropping and recreating the table on my local database, but it made no difference.

I've tried SF_ReplicateLarge and SF_ReplicateIAD. Sometimes I'm able to get it to run, but it always takes a long time.

Anything you guys can think of as to why this specific table is so slow to query through the DBAmp?
1 person has
this question
+1
Reply
  • Normally, this table is heavily impacted by the security model of salesforce. If you use a non- system admin to do the query, the salesforce has to perorm the security calculations to see if the salesforce user can see each record.

    Did you use the exact same salesforce user id on the workbench as the linked server uses ?

    Is the linked server salesforce user a salesforce system admin ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • I didn't realize at first, but I did use different users when logging into Workbench vs the DBAmp user. I tried logging into Workbench with the same user this time, but had the same results as with the other user (query finished in 1-2 seconds).

    When you say a "salesforce system admin", do you mean the user's in a profile that has this system permissions: "View All Data"? Or do you mean a user that has the default "System Administrator" profile?

    The user is in a profile that has System Admin type permissions (including View All Data), and all access to all objects that I can see, but not in the system-default System Administrator profile.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • "QUERY_TIMEOUT: Your query request was running for too long. "

    To be clear, this error message is coming from the salesforce server. The salesforce server is timing out the query because it is consuming too many resources on the salesforce server. DBAmp is merely relaying the error message from salesforce.

    "View all Data" is the needed permission.

    I am not sure the reason for the time difference in your test but the salesforce server does have an in memory cache for results. It is possible that your first query took awhile but got the result into the local cache. Then the second query basically ran from the cache.

    To do a true apples - to -apples test, try using the salesforce data loader with the same salesforce user and the bulkapi switch turned off and export all rows/columns of the object. Then run a sf_replicate and compare the times.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Good idea. I tested this in Data Loader (and ensured Bulk API was turned off), and the export (using this: Select Id FROM EventWhoRelation LIMIT 100) was somewhat slow: 10 seconds instead of 1-2 seconds from Work Bench, but faster than through SQL Server (27 minutes). The strange thing is that from SQL Server, I'm not running SF_Replicate to reproduce the slow query, all I'm doing is selecting the first 100 rows (Select TOP 100 Id From Salesforce...EventWhoRelation), so I would expect that to be fairly quick, unless I'm missing something.

    The memory cache is a good thought, however, I've tried multiple times from both Workbench and as a SQL Query, and I'm consistently getting the same results and time differences between the 2. In Workbench, I do notice it slow the first time (5-6 seconds), and then fast after that (1-2 seconds), so it does look like caching is happening, but it does not seem like that's what is causing the difference.

    I do realize this timeout message is coming from Salesforce side. I'm just not sure why this specific table is so much slower to query in this way. Your indication that this table is heavily impacted by the security model of Salesforce is a good thought, I was hoping it would be that as it makes a bit more sense, since other tables are much faster to query. I've tried querying with various admin users but there hasn't been any difference. Do you know there's any specific permission in the profile that would bypass this security model? I would think view all on the object, or the View All Data system permission would do the trick, but that doesn't seem to be the case here.

    Something new I found was if I filter the query to not include records with isDelete = TRUE (even though there are no records like this), it is significantly faster. This query runs successfully in about 40 seconds:
    select Top 100 Id from SALESFORCE...EventWhoRelation WHERE isDeleted = 'FALSE'

    It is consistently returning results in 40 seconds if I'm querying 10, 100, 1000 records. So it sounds like I should just skip using SF_Replicate on this table, and just use my own query to pull that data.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • Test results with just selecting the id are not really useful. This is because these things are not really tables on the SF architecture.

    I am more interested in how long it takes the data loader to download all columns.

    Also, can you verify that the DBAmp provider options are set according to Chapter 1. In particular is "allow in process" checked?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Also, is collation compatible set to true for the linked server?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • Collation Compatible is set to True and I verified all the Provider options in Chapter 1 are configured as specified including "Allow in Process" being enabled.

    I ran an Export All from Data Loader with all fields selected:
    Select Id, RelationId, EventId, AccountId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, IsDeleted, Type FROM EventWhoRelation

    The export completed in roughly 30-40 seconds. Let me know if that's not what you meant by downloading all columns from Data Loader.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Do you have time now for for web meeting? Please send your email to support@forceamp.com
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • Thanks for connecting with me on this, Bill.

    The problem does seem to be that this table (EventWhoRelation) is slow through the API (not specifically via DBAmp as Data Loader does actually exhibit these problems under the same circumstances), but once it's properly cached into memory in Salesforce, it's fast.

    For now, I've set this table up as a separate SQL Job to retry a few times to work around that. But I'll probably connect with Salesforce about why this table is slow when I get some more info and have time to work on that issue.

    Thanks!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • We are seeing similar issue with TaskWhoRelation. The schedule has been running without issue for two years (using same user & profile with viewall permission) and started Query time out error few weeks ago. That table has 20K+ records and no major updates or inserts into the object. The refresh fails intermittently even after re-replicating the object.Any thoughts on how to resolve this? Thanks
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • As the thread indicates, the timeouts are usually on the salesforce side which means there is not much DBAmp can do.

    In summary:

    1. Make sure you are using a sf user who has View All Data.
    2. Setup the job step with this table separate from the others and add a retry of 3 times to take advantage of the memory cache on salesforce.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned