+ Start a Discussion
ss123ss123 

query timing out anyother way to use same query

All contacts from Account name =0017000000qz98bAAA with no case attched to them will be deleted

it is timing out

 

code for query is in following message

 

purge code not working in production but it is working in Full sandbox

 

 

 

 

 

SELECT Id FROM Contact WHERE CreatedDate <=:startDate  AND AccountId = '0017000000qz98bAAA' AND  id not in (select ContactId from Case) LIMIT 10000]

Rahul_sgRahul_sg
Roughly how many contacts are there for this account(0017000000qz98bAAA)
ss123ss123

millions and millions

so i need to use more ands to make the execution time less ot need a diffrent query with same output to use with data loader

Rahul_sgRahul_sg
Ohh ok.

Its difficult to process such big no. of records in apex without a batch job.

I see few options here.
1) Create a report in SFDC , run that report and extract the csv file . Use it to delete the records using the data loader.
2) Put more filters in your query.
The problem with your query is even if you have a limit 10000 it has to scan through the entire case object ( this object also could have millions of rows)
Try to Save 10000 records (Contacts in a list....by applying first 2 filters)
and then right another query on case object to scan these 10000 records.
Now you will get 1 more list.
Difference between these 2 lists can be used to delete the required contact records.
ss123ss123

Thanks let me try this see if this will work

what about creating a list of only few records and running that query on thta list is that possible

AsitM9AsitM9

You can break your query like this it may improve the performance.

set<id> CaseId=new set<id>();
for(id ii: [select ContactId from Case].id){
    CaseId.add(ii);
}

SELECT Id FROM Contact WHERE CreatedDate <=:startDate  AND AccountId = '0017000000qz98bAAA' AND  id not in :CaseId LIMIT 10000]

 

Peter_sfdcPeter_sfdc

Looking at your query, I would suspect that this clause is your problem: 

 

 id not in (select ContactId from Case)

 How many case records are in your system? I suspect it is timing out because you are querying all of them in this subquery.