WITH
cust AS
    (SELECT DISTINCT
            C.swCustomerId AS EnduserId,
            A.AssetID AS asset,
            suite.ctName AS Product
     FROM ReplicaCADS.dbo.TransactionHeader TH WITH (NOLOCK)
          INNER JOIN ReplicaCRMDB.dbo.SW_CUSTOMER C WITH (NOLOCK) ON C.swCustomerId = TH.EndUserCustomerId
          INNER JOIN ReplicaCADS.dbo.Asset A WITH (NOLOCK) ON TH.TransactionId = A.TransactionId
                                                          AND A.Status = 'Active'
          INNER JOIN ReplicaCADS.dbo.AssetComponent AC WITH (NOLOCK) ON AC.AssetId = A.AssetId
                                                                    AND AC.PrimaryFlag = 1
                                                                    AND AC.Status = 'Active'
          INNER JOIN ReplicaCADS.dbo.MaintenanceProgram MP WITH (NOLOCK) ON MP.AssetId = A.AssetId
                                                                        AND IsLatest = 1
                                                                        AND MP.Status = 'Active'
                                                                        AND MP.EndDate <> '2099-12-31 00:00:00.000'
                                                                        AND MP.MaintenanceType = 'Core'
          INNER JOIN ReplicaCRMDB.dbo.[ct_Product_Suite] suite WITH (NOLOCK) ON suite.ctSuiteID = A.ProductSuiteID
                                                                            AND suite.cTName LIKE 'DaaS'
          INNER JOIN Salesforce.[dbo].[Apttus__APTS_Agreement__c] agr ON agr.Vantive_Org_ID__c = C.ctOrgId
                                                                     AND Apttus__Status__c = 'Activated'
                                                                     AND Agreement_Type__c = 'Licensing'
                                                                     AND agr.Account_Geo__c LIKE 'APAC'
     WHERE NOT EXISTS (SELECT 1
                       FROM salesforce..Priority_Customer__c pr
                       WHERE pr.Account_Org_Id__c = C.CtOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM ReplicaTransactionData..[Transaction] TN
                       WHERE TN.CustomerId = C.swCustomerId
                         AND Status = 'Pending'
                         AND QuoteType IS NULL)
       AND NOT EXISTS (SELECT 1
                       FROM [Salesforce]..Large_Customer__c LDC
                       WHERE LDC.Org_ID__c = C.ctOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM [Salesforce].dbo.Account sac
                       WHERE sac.Org_ID__C = C.ctOrgId
                         AND High_Touch_Account__C = 'true')
       AND NOT EXISTS (SELECT 1
                       FROM salesforce..Priority_Customer__c pr
                       WHERE pr.Account_Org_Id__c = C.ctOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM Salesforce..Asset_Maintenance_Program__c
                       WHERE frmAccount_Org_ID__c = C.ctOrgId
                         AND Maintenance_Type__c IN ('Advanced'))
       AND EXISTS (SELECT 1
                   FROM ReplicaCADS..AssetpricingData AP
                   WHERE AP.AssetId = A.AssetId))
SELECT TOP 1
       P.swLogin AS LoginId
FROM cust
     INNER JOIN ReplicaCRMDB.dbo.SW_PERSON P WITH (NOLOCK) ON P.swCustomerId = EnduserId
                                                          AND P.swStatus = 'Current'
                                                          AND SWLogin IS NOT NULL
                                                          AND P.ctLocale = 'en-US'
     INNER JOIN ReplicaCRMDB.dbo.CT_CONTACT_TYPE Contact WITH (NOLOCK) ON Contact.swContactId = P.swPersonId
     INNER JOIN ReplicaCRMDB.dbo.CT_MC_USERS MCUsers ON MCUsers.swPersonID = P.swPersonId
                                                    AND (MCUsers.ctPassword = '32CA9FC1A0F5B6330E3F4C8C1BBECDE9BEDB9573'
                                                      OR MCUsers.ctPassword = '')
ORDER BY NEWID();

When trying to use both the below condition together its taking a long time

AND NOT EXISTS (SELECT 1
                  FROM Salesforce..Asset_Maintenance_Program__c
                  WHERE frmAccount_Org_ID__c = C.ctOrgId
                    AND Maintenance_Type__c IN ('Advanced'))
AND EXISTS (SELECT 1
            FROM ReplicaCADS..AssetpricingData AP
            WHERE AP.AssetId = A.AssetId)

Can you please help me in someway tweaking the above query to get the result in quick time

CodePudding user response：

This can be massively helped by understanding how SQL works. But I would recommend that you remove most of the text fields in the joins, and using their int values instead that I suppose exist.

For instance - High_Touch_Account__C = 'true', this should probably be stored as a BIT inside of the DB, and as such, 1 or 0 would be the way to go, not 'true'. Similarily the Status = 'Active' should probably be replaced with using the int value for 'Active'.

Regarding the and not exists, I would probably create a temporary table at the start that gathers all of the things that you do not want in there, then simply do a left join and then "where join is null" basically. This could replace 25% of your code.

NOLOCK might also be something that you should look into.

If you upload the files with the data, It would be easier however to give you a reply on the most optimal way to do this, but as it sits, we've got no idea of what data exists.