>Hi All,
>
> I've run into a problem which has me intrigued. I have the following SQL Select which takes 20 seconds to run on a 12,500 record DBF even though UPPER(contacts.lastname) is Indexed.
>
>

>SELECT contacts.contactid, contacts.companyid ;
>    FROM contacts ;
>    WHERE UPPER(contacts.lastname) = ?PADR(UPPER(loadfile.f),LEN(contacts.lastname)) ;
>    INTO CURSOR contacts_found_cursor
>

>
>If I change the SQL to the following it only takes 0.01 of a second to run.
>
>

>SELECT contacts.contactid, contacts.companyid ;
>    FROM contacts ;
>    WHERE UPPER(contacts.lastname) = ?PADR(UPPER(loadfile.f),EVAL("LEN(contacts.lastname)"))
>

>
>The following also only takes 0.01 of a second to run.
>
>

>cLastName = PADR(UPPER(loadfile.f),LEN(contacts.lastname))
>
>SELECT contacts.contactid, contacts.companyid ;
>    FROM contacts ;
>    WHERE UPPER(contacts.lastname) = cLastName
>

>
>It appears that the first example is not being optimised but the second and third ones are. Any ideas as to why the first one isn't optimised? Could it be because the SQL sees the Contacts Table on both sides of the Where Clause?
>
>Thanks,
>Gavin...

It's obviously better to prepare all constants before SQL than caluclating them in SQL for each record. So, the third example is the best here. Also I would get rid of aliases, since you're using only one table in your SQL.