Help get this topic noticed by sharing it on Twitter, Facebook, or email.
I’m anxious

Large table slow query

This is the resultset I am trying to get, but the query is taking forever.

SELECT OCR.OpportunityId, CM.CampaignID,CP.Id, CP.Name CampaignName, CP.Type as CampaignType
FROM SALESFORCE...[OpportunityContactRole] OCR
INNER JOIN SALESFORCE...[CampaignMember] CM ON CM.ContactId = OCR.ContactId
INNER JOIN SALESFORCE...[Campaign] CP ON CP.Id = CM.CampaignID
WHERE CP.TYPE like '%Territory%'

As soon as I joing CampaignMember table, the query slows down drastically.

By running the following query:
select * from openquery(SALESFORCE,'Select * from CampaignMember')
Number of rows returned: 1,114,018
Time: 16mins 36 secs

Please advice, what can I do to speed up my main query!?
1 person has
this problem
+1
Reply
  • I can help recode as a SOQL statement but I am confused with the result you want.

    Why join in the OpportinityContactRole table when you select no columns from it (assuming we can pickup the Opportunity Id directly from the Opportunities related to the campaign itself) ?

    Perhaps you could state in English the result you want, allowing us to rework the joins as needed to get that. SOmething like "I want all the contacts who have a role in Opportunities that were part of Campaigns where the type was "%Territory%"
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I am sorry about the lack of details on the initial post. The query that I had posted first was a subquery of the main query below. See the Left Join to the sub query aliased NC (National Campaign) is just the first part of the query that I am trying to build. Once I get around the performance issue I would like to get Counts of the different Types of Campaigns associated with the Opportunity. The reason why I am joining OpportinityContactRole table, is becuase I was told the way to link the Oppurtinity to the Campaign is by linking OpportinityContactRole and CampaignMemeber tables.

    SELECT O.ID
    ,NAME AS OpportunityName
    ,Services__c AS Services
    ,CASE
    WHEN Services__c = 'Center'
    THEN BH_ROS_REVENUE__c
    ELSE Amount
    END AmtRev
    ,Amount
    ,BH_ROS_REVENUE__c AS BHROS
    ,StageName AS Stage
    ,O.CreatedDate AS OppCreatedDate
    ,StageName
    ,NC.CntNC CountOfNationalCampaigns
    FROM SALESFORCE...[Opportunity] O
    LEFT JOIN (
    SELECT OCR.OpportunityId
    ,Count(CM.CampaignID) CntNC
    FROM SALESFORCE...[OpportunityContactRole] OCR
    INNER JOIN SALESFORCE...[CampaignMember] CM ON CM.ContactId = OCR.ContactId
    INNER JOIN SALESFORCE...[Campaign] CP ON CP.Id = CM.CampaignID
    WHERE CP.TYPE LIKE '%Territory%'
    GROUP BY OCR.OpportunityId
    ) NC ON NC.OpportunityId = O.ID
    WHERE Record_Type_Name__c = 'Pipeline'

    Please let me know if you need additional details. And thanks a lot for your help!!!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • This is a really tough query to figure out. Here is a fragment that may help. I was trying to build a SOQL statement that tied all the relevant tables together.

    Note that is query includes contacts who were members of a campaign and contact roles of an Opportunity.

    select * from
    openquery(salesforce,
    'Select Id,Name,
    (select ID,Role, Opportunity.Name, Opportunity.Amount, Opportunity.StageName From OpportunityContactRoles ),
    (select ID,Status, FirstRespondedDate, Campaign.Name From CampaignMembers )
    From Contact
    where Id in (Select ContactId from CampaignMember where Campaign.Type like ''%Territory%'') AND
    Id in (Select ContactId from OpportunityCOntactRole)')
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • So my end result is to build an SSRS report. What are my other options here, other than learning SOQL and writing this complex query? Can I download the data using DBAmp replicate and refresh stored procs, and then writing an optimized SQL view? Will I see the same performance issues using that method as I saw when using four part identifier and quering the linked server? Please explain, I am very new to using DBAmp so not exactly sure how it works. Thank you.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • There are certainly other approaches. One question would be how often will the report run and is it critical for the report to be "real-time" as opposed to current as of the last sf_refresh .
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I’m excited
    Good question, this report will only be run monthly and it could be current as of a certain refresh time, in other words we could set it up to refresh at off bussiness hours, if thats what you are referring to. Please advice steps for the approaches you are thinking. Thank you!!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • So with a once a month schedule you should just go ahead and do a sf_replicate. This is because salesforce only keeps deleted row info for 10 days and that would force you to run a sf_refresh every 10 days.

    I guess the main thing is the size of your CampaignMember table. Of the 1 million or so rows, how many would you say meet the criteria of your where clause (i.e. with Campaign name of %Terroritory%. If that is a small number then rather than doing a sf_replicate it would be better just to pull the data you want:

    Select *
    into CampaignMember_Subset
    from Openquery(SALESFORCE,
    'Select *, Campaign.Name, Campaign.Type from CamaignMember
    where Campaign.Type like ''%Territory%'' ')
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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