>I have a weird problem with the optimization and I cannot find what is causing this.
>
>I have a SQL command that is having several INNER JOINs. So, the command is like this:
>
>

>SELECT Member.Numero,Status.Name FROM Member;
> INNER JOIN Status ON Member.NoStatus=Status.Numero;
> WHERE Member.NumeroI=4112;
> ORDER BY 1 ASC
>

>
>The SQL command has been simplified for the example.
>
>If I use the command as is, the result is not optimized. I could take like 1 to 3 seconds to obtain the result. I can see sometime the progress bar showing the progress or if under a second, I can see something like 0.94, 0.92, etc.
>
>But, if I change one field in the SQL, like this:
>
>

>SELECT Member.Numero,Status.Numero FROM Member;
> INNER JOIN Status ON Member.NoStatus=Status.Numero;
> WHERE Member.NumeroI=4112;
> ORDER BY 1 ASC
>

>
>The result is fully optimized. I get the result in 0.00 second.
>
>So, why changing the field from Status.Name to Status.Numero would cause such a reaction?

Because you use an INNER JOIN,
this:

SELECT Member.Numero,Status.Numero FROM Member;
 INNER JOIN Status ON Member.NoStatus=Status.Numero;
 WHERE Member.NumeroI=4112;
 ORDER BY 1 ASC

is equal to:

SELECT Member.Numero,Member.NoStatus FROM Member;
 INNER JOIN Status ON Member.NoStatus=Status.Numero;
 WHERE Member.NumeroI=4112;
 ORDER BY 1 ASC

If Status.Numero is indexed,
the exists() condition require ( one or many ) seek on the index ( except for the deleted() condition ).
and the Status Table can to be ignored.

SQL Server works so, but it seems me strange that VFP has these optimizations.

compare the INNER with the LEFT JOIN