: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows)

Question

I was wondering if someone can help me with the following queries. I'm getting the following errors when I try to run my trigger.

The Records I'm returning is only 28 records:

: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows).

Please find below my soql:

Select 
    id, Current_Period__c, current_fiscal_year__c, Payment_Status__c, Fiscal_Year__c, 
    Period__c, Cost_Centre__c, Expense_Code_Advance_Payments__c, Expense_Code_Monthly_Payments__c, 
    Amount_Due__c, Employee_Lastname__c, Employer__c, RectypeId__c, Employer_OA_Code__c, Timesheet_Date__c 
From 
    Employer_Payment__c 
Where 
    Emp_Pay_Rate__c = :run.Employer_Payment_Rate_Id__c
    AND Timesheet_Date__c >= :Date.valueOf(run.Timesheet_Start_Date__c) 
    AND  Timesheet_Date__c <= :Date.valueOf(run.Timesheet_End_Date__c) 
    AND Type__c = :run.Payment_Type__c 
    AND payment_status__c = 'Scheduled' 
    AND Hold_All_Payments__c=FALSE 
    AND Advance__c = TRUE
    AND Employer__c <> null

Answer 1

The problem is that to find the 28 rows the database has had to look at more than 200,000 rows and so this exception is thrown because that is a very time consuming thing for the database to do and there are strategies to avoid that cost.

You can Google for the error message and find out about ways to diagnose and fix the problem. Broadly, you need to make sure that the where clause limits the rows as much as possible and typically that means referencing columns that have indexes. Sometimes it is also necessary to get Salesforce support to add a custom index.

Also see this knowledge article Make SOQL query selective as a starting point.