Constructing the WHERE clause

In an APS query, the WHERE clause is processed as written—from left to right. You should always place your indexed clauses first, in a least-to-most records returned order, followed by any non-indexed clauses. Placing a non-indexed clause first forces a record-by-record search on the entire APS, regardless of how many indexed filters you have provided.

Even if you have an all-indexed WHERE clause, you should construct it to read left to right an a least-to-most fashion in order to reduce internal processing costs. For example, the following query will return all successful instances for all reports, and then find the ones with a parent ID matching a report ID. This could retrieve thousands of instances instead of just a few.

Select SI_ID From CI_INFOOBJECTS Where SI_SCHEDULE_STATUS = 1 AND SI_PARENTID = ReportID

A better way to write the same query would be as follows.

Select SI_ID From CI_INFOOBJECTS Where  SI_PARENTID = ReportID AND  SI_SCHEDULE_STATUS = 1

Note:    

• You cannot use SchedulingInfo and ProcessingInfo properties directly in a WHERE clause; however, you can use specific sub-properties as filters. For further details, see ProcessingInfo properties and SchedulingInfo properties.
• In a WHERE clause, you must specify Boolean and Date/Time values using a particular format.