Hi Robert,

As Sergey pointed out, the query is not optimized when SET ANSI is OFF. It is a BUG, the STR() function is mistakenly treated as a function returning result of variable length. VFP9 doesn't use indexes on variable length expressions to optimize inexact equal conditions (SET ANSI OFF). They were used in previous versions and the result could be wrong.

To make query optimizable, you can SET ANSI ON or use PADR function.

select Lit_LosFK, Lit_Date ;
   from LOSSITEM ;
   where str(Lit_LosFK, 6) + dtos(Lit_Date) in ;
      (select PADR(str(Lit_LosFK, 6),6) + min(dtos(Lit_Date)) from LOSSITEM group by Lit_LosFK) ;
   group by 1, 2 ;
   into cursor curFIRSTLOSSPAYMENT1 nofilter

Thanks,
Aleksey.

>In VFP 9, why would this first select statement run very much slower than the last 2 select statements?
>
>In VFP 8 the first select statement runs very quickly. In VFP9 it is extremely slow.
>
>

>select Lit_LosFK, Lit_Date ;
>   from AI!LOSSITEM ;
>   where str(Lit_LosFK, 6) + dtos(Lit_Date) in ;
>      (select str(Lit_LosFK, 6) + min(dtos(Lit_Date)) from AI!LOSSITEM group by Lit_LosFK) ;
>   group by 1, 2 ;
>   into cursor curFIRSTLOSSPAYMENT1 nofilter
>
>
>select str(Lit_LosFK, 6) + min(dtos(Lit_Date)) as TmpKey ;
>   from AI!LOSSITEM ;
>   group by Lit_LosFK ;
>   INTO CURSOR curTemp nofilter
>
>select Lit_LosFK, Lit_Date ;
>   from AI!LOSSITEM ;
>   where str(Lit_LosFK, 6) + dtos(Lit_Date) in ;
>      (SELECT TmpKey FROM curTemp) ;
>   group by 1, 2 ;
>   into cursor curFIRSTLOSSPAYMENT2 nofilter
>