Reasonable changes all, Michael, but shouldn't they have similar effects on VFP5 as VFP7?

In other words, how do you expect the VFP7 execution time to approximate the VFP5 time with these changes?


>OK, First let's get those IIF statements out of the SELECT. Use the IIFs BEFORE the select to expand your cWhereSyntax string to include the two ANDs (or to create two more strings for macro substitution). The reason is you are forcing your SELECT to re-evaluate the IIF's for each and every record where it's not necessary. Let us know if you need clarification.
>
>Do the same for DATE(): before the SELECT, create "dToday = DATE()" and replace DATE() with dToday in your SELECT. I don't expect this will make as much of a difference as my first suggestion, but it SHOULD help.
>
>Let us know the timing results after those changes (I think it will be quite significant) - we can trim more time.
>
>Also, after those changes (and after grabbing your new timing numbers), use SYS(3054). You can check the help file to see how to use it, or search this message board for more info on the command.
>
>As well as the timing results, let us know the results of your SYS(3054). It will help us match the select statement to your indexes.
>
>>Hi Michael;
>>Thats a very confident Wow!.
>>here is the sql to see if you can optimize it and take a few seconds off.
>>
>>cWhereSyntax = "ARF.pat_id = Pr.pat_id and ARF.sect = lcSect " + " and Mdl = 'GL'"
>> the above is one option of a do case statement.
>>
>>SELECT ARF.Accn, ARF.Pat_ID, Pr.pat_name, ARF.Drawn_date, ARF.Drawn_tm, ;
>> ARF.Req_code, ARF.rs, (DATE() - Drawn_date ) AS Days ;
>> FROM ARF, Pr;
>> WHERE &cWhereSyntax ;
>> AND IIF(lnPndgOnly=1,ARF.rs IN ("O","R"), ARF.rs = "") ;
>> AND IIF(!empty(lcPat_Id),ARF.Pat_Id=lcPat_Id, ARF.Pat_Id="") ;
>> AND BETWEEN(Drawn_date,ldDateFrom, ldDateTo ) ;
>> ORDER BY ARF.Accn, ARF.Req_code ;
>> GROUP BY ARF.Accn, ARF.Req_code ;
>> INTO CURSOR cPndgTsts
>>The above query took 8 seconds with VFP5 and 15 seconds in VFP7 both installed in the same PC PIII 1GH with 128 MB ram.
>>The ARF table is 80,000 to 100,000 records. It has 75 fields and a length of
>>476.
>>
>>Thanks.