ERROR: Query failed: Timeout in automation studio

Question

I am running the query many times and keep receiving Time out error.

Please help me to solve the issue. Below is my sql query.

SELECT
    Opp.Id AS OpportunityId,
    payer.id as contactid,
    Payer.MDS_Preferred_House_Name__c AS [PAYER_PREFERRED_HOUSE_NAME],
    Payer.Mailingcity  AS [PAYER_CITY],
    Payer.MailingCountry AS [PAYER_COUNTRY],
    Payer.Mailingpostalcode AS [PAYER_POSTCODE],
    Payer.Salutation AS [PAYER_TITLE],
    Payer.FirstName AS [PAYER_FIRST_NAME],
    Payer.LastName AS [PAYER_LAST_NAME],
    CONCAT(Payer.Salutation,' ',Payer.LastName) AS [PAYER_POST_NAME],
    CONCAT('Dear',' ',Payer.Salutation,' ',Payer.LastName) AS [PAYER_SALUTATION],
    Payer.MDS_Supporter_ID__c AS [PAYER_SUPPORTER_NUMBER],
    
    Opp.MDS_Joint_Postname__c AS [MEMBERSHIP_POST_NAME],
    
    Pri.MDS_Supporter_ID__c AS [PRIMARY_SUPPORTER_NUMBER],
    Pri.FirstName AS [PRIMARY_FIRST_NAME],
    concat(Pri.Salutation,' ',Pri.LastName) AS [PRIMARY_POST_NAME],
    
    Opp.MDS_Membership_Type__c AS [MEMBERSHIP_TYPE_DESCRIPTION],
    Opp.Amount AS [MEMBERSHIP_SUBSCRIPTION_RATE],
    Opp.MDS_Count_of_Cards__c AS [NUMBER_OF_CARDS],
    /*This should be transformed to be cash or DD, anything not DD should be shown as Cash.*/
    CASE WHEN Opp.npsp4hub__Payment_Method__c != 'Direct Debit' THEN 'Cash' ELSE  Opp.npsp4hub__Payment_Method__c 
        END AS [MEMBERSHIP_PAYMENT_METHOD],
    Opp.MDS_Membership_Number__c AS [MEMBERSHIP_NUMBER],
    Opp.MDS_Send_Order_to_Recipient__c AS [MEMBERSHIP_SEND_TO_RECIPIENT],
    Opp.npe01__Membership_Start_Date__c AS [MEMBERSHIP_RENEWAL_DATE],
    payer.MailingCountry, 
    Opp.npsp4hub__Payment_Method__c, 
    Ind.MDS_Do_Not_Contact__c, 
    Opp.MDS_Installment_Period__c, 
    Opp.MDS_Membership_Type__c, 
    Opp.MDS_Indicator_For_Membership_Change__c,
'No code' AS [SEGMENT_CODE]
    
FROM ENT.[Opportunity_Salesforce_1] Opp
INNER JOIN ENT.[Contact_Salesforce_3] Payer
    ON Payer.Id = Opp.MDS_Payer__c
INNER JOIN ENT.[Contact_Salesforce_3] Pri
    ON Pri.Id = Opp.npsp__Primary_Contact__c
INNER JOIN ENT.[Individual_Salesforce_1] Ind
    ON Ind.Id = Payer.IndividualId
    

WHERE
    Opp.MDS_RecordType_Name__c = 'Gift_Membership'
    AND
    Opp.StageName IN ('Pending', 'Purchased')
    AND
    Opp.MDS_Do_Not_Renew__c = 'False'
    AND
    Payer.npsp__Deceased__c = 'False'
    AND
    Payer.MailingPostalCode NOT IN ('SL6 0GA', 'SL6 0GB')
    AND
    DATEDIFF(month, GETDATE(), Opp.npe01__Membership_Start_Date__c) = 0
    AND
    Payer.MDS_Gone_Away__c = 'False'
    AND
    Opp.MDS_Renewal_Status__c = 'Stage 1'

Answer 1

I'd just split it at the join level. First query is an overwrite on the target DE. Second and third queries are updates.

Query 1 - Opportunity

select
  Opp.Id AS OpportunityId
, Opp.MDS_Joint_Postname__c AS [MEMBERSHIP_POST_NAME] 
, Opp.MDS_Membership_Type__c AS [MEMBERSHIP_TYPE_DESCRIPTION]
, Opp.Amount AS [MEMBERSHIP_SUBSCRIPTION_RATE]
, Opp.MDS_Count_of_Cards__c AS [NUMBER_OF_CARDS]
, CASE 
    WHEN Opp.npsp4hub__Payment_Method__c != 'Direct Debit' THEN 'Cash' 
    ELSE  Opp.npsp4hub__Payment_Method__c 
  END AS [MEMBERSHIP_PAYMENT_METHOD]
, Opp.MDS_Membership_Number__c AS [MEMBERSHIP_NUMBER]
, Opp.MDS_Send_Order_to_Recipient__c AS [MEMBERSHIP_SEND_TO_RECIPIENT]
, Opp.npe01__Membership_Start_Date__c AS [MEMBERSHIP_RENEWAL_DATE]
, Opp.npsp4hub__Payment_Method__c
, Opp.MDS_Installment_Period__c
, Opp.MDS_Membership_Type__c
, Opp.MDS_Indicator_For_Membership_Change__c
, 'No code' AS [SEGMENT_CODE]
, Opp.MDS_Payer__c
, Opp.npsp__Primary_Contact__c
FROM ENT.[Opportunity_Salesforce_1] Opp
WHERE
Opp.MDS_RecordType_Name__c = 'Gift_Membership'
AND Opp.StageName IN ('Pending', 'Purchased')
AND Opp.MDS_Do_Not_Renew__c = 'False'
AND DATEDIFF(month, GETDATE(), Opp.npe01__Membership_Start_Date__c) = 0
AND Opp.MDS_Renewal_Status__c = 'Stage 1'
/* action: overwrite */ 

Query 2 - Contact

select
  Opp.OpportunityId
, payer.id as contactid
, Payer.MDS_Preferred_House_Name__c AS [PAYER_PREFERRED_HOUSE_NAME]
, Payer.Mailingcity  AS [PAYER_CITY]
, Payer.MailingCountry AS [PAYER_COUNTRY]
, Payer.Mailingpostalcode AS [PAYER_POSTCODE]
, Payer.Salutation AS [PAYER_TITLE]
, Payer.FirstName AS [PAYER_FIRST_NAME]
, Payer.LastName AS [PAYER_LAST_NAME]
, CONCAT(Payer.Salutation,' ',Payer.LastName) AS [PAYER_POST_NAME]
, CONCAT('Dear',' ',Payer.Salutation,' ',Payer.LastName) AS [PAYER_SALUTATION]
, Payer.MDS_Supporter_ID__c AS [PAYER_SUPPORTER_NUMBER]
, Payer.MailingCountry
, Payer.IndividualId
FROM TARGETDENAME Opp
INNER JOIN ENT.[Contact_Salesforce_3] Payer ON Payer.Id = Opp.MDS_Payer__c
where 
Payer.npsp__Deceased__c = 'False'
AND Payer.MailingPostalCode NOT IN ('SL6 0GA', 'SL6 0GB')
AND Payer.MDS_Gone_Away__c = 'False'
/* action: update -- assuming the target DE's primary key is only OpportunityId */

Query 3 - Individual

select
  Opp.OpportunityId
, Ind.MDS_Do_Not_Contact__c
FROM TARGETDENAME Opp
INNER JOIN ENT.[Individual_Salesforce_1] Ind ON Ind.Id = Payer.IndividualId
/* action: update -- assuming the target DE's primary key is only OpportunityId */