I am certainly no expert in SQL optimization, but in your example it seems obvious that the difference is in the inclusion of the Status.Numero in the second query. I would guess that including it as an additional parameter, rather than as a replacement, might also see the improved performance.

As a pure guess, I would speculate that it in the first case it might be doing the join before the selection, then selecting on the result because the join key is not in the result set. Whereas in the second case it can do the join after the selection because the key is included.

Have you tried

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

...Jim

>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?