Quite true, but the difference between 8 and 16 seconds is a lot harder to accept than the difference between 1 and 2 seconds - dontcha think?

As a matter of fact, one can compare the old app at 8 seconds with the new app with better SQL syntax at maybe 2 seconds <g>. Yes, it's Politics, not programming.

Kidding aside, I really DO expect response time to speed up _dramatically_ with those changes. I also expect that VFP5 will probably still be a little faster if the same changes are made there.

Admittedly, once we optimize the syntax it may be interesting to break it down to see what parts of the statement are slower in 7 - track down the slow components, as it were. I wouldn't even bother, tho, until the statement is well optimized. You don't adjust the carburetor before you change the plugs. Ooops - we don't USE carburetors any more, do we?

I don't have VFP5 on my machine any more (and I don't really want to re-install), so I couldn't do the testing between versions here. Have you done any speed comps to shed light on the issue?

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