>Hi all,
>
>I have just finished implementing GENERAL indexes on a client's data so that they get "culturally correct sorting". For their client's table, I changed a few of the indexes from:
>
>INDEX ON UPPER(Lastname) TAG Lastname
>
>to the new and improved:
>
>INDEX ON Lastname TAG Lastname COLLATE General
>
>This works fine for viewing records but now I have lost rushmore optimization on a few queries. This *used* to be optimizable under machine indexes:
>
><

>
>
>SELECT Lastname, Firstname ;
>   FROM Lawyers ;
>   WHERE UPPER(lastname) == UPPER(lcLastname) AND ;
>      UPPER(Firstname) == UPPER(lcFirstname) ;
>   INTO ARRAY laNames
>
><

>
>
>It was optimizable because the left side of the WHERE matched a tag. But now the tags do not include UPPER(). Users noticed a slowdown and so I investigated using SYS(3054) and found out that the above expression is no longer optimized. The other things I tried were:
>
>1) removing UPPER() from the left side of the above equation; did not help and wrong results
>
>2) adding the UPPER() back to the tag so that it matched but this did not help.
>
>Any way to get this optimized?
>
Hi Albert

You will have to SET COLLATE TO GENERAL before doing the SELECT. Says so right in the help. :)

While you're doing that you might also upper your memory variable once in advance of the query rather than per row.

SET COLLATE TO GENERAL
lcLastName = UPPER(m.lcLastName)
lcFirstName = UPPER(m.lcFirstName)

SELECT Lastname, Firstname ;
FROM Lawyers ;
WHERE UPPER(lastname) == m.lcLastname AND ;
UPPER(Firstname) == m.lcFirstname ;
INTO ARRAY laNames

HTH