T
Tim Hutchens
Guest
In my opinion, the biggest performance impact here is related to index selection. Given that there is an index with table.active as a leading component, what index selection does the COMPILE XREF show? The XREF for each of the following shows the same index selection of an index with table.active as a leading component: FOR EACH table WHERE table.active = YES NO-LOCK: FOR EACH table WHERE table.active NO-LOCK: FOR EACH table WHERE table.active = NO NO-LOCK: The XREF for this query shows WHOLE-INDEX: FOR EACH table WHERE NOT table.active NO-LOCK: According to Dan Foreman, there is no performance difference between these constructs: pugchallenge.org/.../230_Indexing.ppt (slide 19) FOR EACH table WHERE table.active = YES NO-LOCK: FOR EACH table WHERE table.active NO-LOCK: If there is a difference, I would attribute it to evaluating the equality expression as Torben mentioned. For the NOT operator, it may have to do with NOT being treated more like a function than an operator as Tom pointed out. I can't find an Order of Operations reference, but that may be the reason the index engine won't deal with the NOT table.active construct. So I think the main recommendations are to make sure there is an appropriate index for the query that includes the logical field; and to not use the NOT operator on logical fields in queries. After that, test performance of the other constructs against the table you are working on. Not sure what impact the presence of null values may have on performance.
Continue reading...
Continue reading...