>Hi Craig!
>
>>
>>Add the following tags:
>>Requests on icasedefid
>>PR_Address on Prov_Id
>
>Sorry I didn't mention the above. Those tags were also part of the tables.
>
>>You'll never get full optimization on the query. See the Rushmore KB article on my website.
>
>Your site is down or for some reason IE can't find it. :(

Hmm...we've been having some problems lately.

>
>>Rushmore will use what ever tag exactly matches the statement on the left-hand side of the =.
>
>Please set me straight on this one:
>
>That means that there is a difference between using something like "prov_id = providers.iid" and "pr_address.prov_id = providers.iid"????

You must exactly match what is there. You'll need a tag on prov_id.

Let's take a common index... UPPER(LastName + FirstName)

If you do:

WHERE UPPER(LastName) = SomeValue, this won't be optimized.

If you do:

WHERE UPPER(Last_Name) + UPPER(FirstName) = SomeValue, this won't be optimized.

If you do:

WHERE UPPER(LastName + FirstName) = SomeValue, this will work.

>
>>Use the FORCE keyword in the SQL SELECT to join tables in the order listed.

You may get some improvement if you reorder the tables in the SQL SELECT. If you leave out FORCE, you have no way of knowing how VPF will do the JOINS. Also, VFP will always join tables two at a time.

Again, a simple example. Given three tables, A, B, and C, VFP could do the joins:

- A+B = D, then join D+C
- A+C = D, then join D+B
- B+C = D, then join D+A

There is no way to know which of the above will be used. You will never get A+B+C.

>
>That made it even slower!
>
>Thanks!
>
>Alex