Hi All,
Just wanted to check with you guys if there is any preferred way of writing a for each loop for an index plus a few more conditions.
I have a customer table with an index on customer.status but no index on customer.region (for example).
Now if I want to list all the customers with a statsu = "A" and a region = "North" then apart from adding a new index on to the table I have 2 alternatives that I can think of.
The 2 alternative that I can see are...
FOR EACH customer WHERE customer.status = "A"
AND customer.region = "North"
NO-LOCK:
/*Do stuff*/
END.
or...
FOR EACH customer WHERE custoemr.status = "A"
NO-LOCK:
IF custoemr.region <> "North" THEN NEXT.
/*Do stuff*/
END.
As far as I can tell these should both do the same thing (i.e. go through EVERY customer with a status of "A" and then check the region to see if it is valid).
Are there any performance gains from using one or the other? Or is there another way altogether that will speed this up?
Thanks for any help
Oliver
I don't think there's a better way from a performance perspective. However, I would go for the 'complete' for each (with the region filter in the query for a couple of reasons:
(1) Readability: it's immediately clear what you're trying to achieve. Hiding the Region eq 'A' code in the FOR block's body means that it's easier to overlook, or to have other conditions before it. If the block is intended to operate on region-filtered records, that criteria should be in the FOR EACh.
(2) Future-proofing, to a degree: if at some point in the future, an index is added that the query can take advantage of, it will do so without any coding required. The second case will never use an index.
$0.02,
-- peter
Peter is right. There's no reason for not doing alternative 1.
Talk to your DBA and check the possibilities of adding an index.
Good luck!
Another alternative might be to use an indexed query to populate a temp-table which has appropriate indexes defined, and then query that. But whether that's feasible depends on your app.
-- peter