MVSFORUMS.com

[bullfighter]

hi

does the order of parameter checks in the Where Clause of an SQL matter the performance.

If Index is defined in the order COL1, COL2, COL3

then would it be more efficient if my where clause has first COL1 check then COL2 check and then COL3 check..??

Thanks

[CZerfas]

Yes, the ordering of predicates in the WHERE clause is relevant for performance. Although, the predicates used to access an index are interpreted first by DB2.

Relevant is this ordering for non-indexed predicates. There the suggestion is to use the one first which filters most. An example: if you don't have indexes for "gender" or "postal code", and you look for male customers living in a particular town, then code the predicate searching for a certain "postal code" before you search for "gender".

This of course is only relevant if you scan millions of records.

regards
Christian

[bullfighter]

thanks christian
_________________
champion is not one who never fails, but one who never quits