How to modify two similar queries to optimize my code

Question

I have two queries that are similar except there's a few differences between them. I was wondering if there's a way to optimize this, by querying this once and still do what I want with my code.

The Code

List<Task__C> tasksToDelete = [SELECT ID, Case__c FROM Task__C
                              WHERE ID NOT IN (SELECT Project_Task__c FROM Time__c 
                              WHERE Project_Task__r.Case__c in :oldCaseIDList) AND Case__c in: oldCaseIDList];
List<Task__c> keepCases = [SELECT ID, Case__c FROM Task__c 
                          WHERE ID IN (SELECT Project_Task__c FROM Time__c 
                          WHERE Project_Task__r.Case__c in :oldCaseIDList)] ; 

for (Task__c: task: keepCases) {
    // do something with the cases associated to the tasks
}
// do something with tasksToDelete 

Answer 1

You can reduce your SOQL usage by one query, at the cost of CPU time and the risk of query row limits. It's not clear whether or not that constitutes an optimization. In my opinion, it almost certainly does not. The context - which limits are most at risk when you call this code? how much data volume is present in these objects? - is key when evaluating whether or not to make that change.

In this situation as in most such you can "combine" these queries

List<Task__C> tasksToDelete = [SELECT ID, Case__c FROM Task__C
                              WHERE ID NOT IN (SELECT Project_Task__c FROM Time__c 
                              WHERE Project_Task__r.Case__c in :oldCaseIDList) AND Case__c in: oldCaseIDList];
List<Task__c> keepCases = [SELECT ID, Case__c FROM Task__c 
                          WHERE ID IN (SELECT Project_Task__c FROM Time__c 
                          WHERE Project_Task__r.Case__c in :oldCaseIDList)] ; 

by writing a single query whose filters are broad enough to encompass both original queries. Unfortunately, here that becomes

List<Task__C> tasks = [SELECT ID, Case__c FROM Task__c];

which is rather likely to run you into trouble with other limits.