SQL Query Taking A Long Time To Run For Result
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
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.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key