Hi!

I shown you the process of how internally SQL Query in VFP works. Yes, locate will not be indexed, so query process will not be optimized too, because it works by similar way.

Try to use query

SELECT hpaph0.* FROM arappx ;
        INNER JOIN hpaph0 ON ;
	hpaph0.aphst_ = arappx.appst ;
	AND hpaph0.aphcty = arappx.appcty ;
	AND hpaph0.aphpol = arappx.apppol ;
	AND hpaph0.aphyr = arappx.appcyr ;
	INTO CURSOR cResult

As you see, I just swap tables in join. In such case VFP during making query will scan arappx table and lookup records from hpaph0 table. Second table is indexed, so lookups will be optimized - query optimized.


And look if it is optimized.

>>Hi!
>>
>>It seems you was not very accurate when reading my message. I'm not propose to use equivalent. I just tried to explain why your query is not optimized. Read it again you will find answer there.
>
>Vlad,
>
>I did read your message and understand it.
>
>Yes, in your example, the arappx did not have indexes, and so of course doing a LOCATE against it without an index would be slow as the loop runs. You mention after your example that if I reverse the loop (loop through arappx and do LOCATEs against hpaph0) then it will be optimized. This is true...the LOCATES will find an index tag on hpaph0 and use it.
>
>That is the exact problem. Though a loop would be optimized, the SQL statement is telling me that there is NO optimization occuring on HPAPH0. That is the part I don't understand. Let me reiterate with a follow-up example:
>
>Here is the query:
>
>SELECT hpaph0.* FROM hpaph0, arappx ;
> WHERE hpaph0.aphst_ = arappx.appst ;
> AND hpaph0.aphcty = arappx.appcty ;
> AND hpaph0.aphpol = arappx.apppol ;
> AND hpaph0.aphyr = arappx.appcyr ;
> INTO CURSOR cResult
>
>Now, in my more recent tries, I have gone one step further. I now have index tags on the following fields:
>
>In hpaph0: aphst_, aphcty, aphpol, apyyr
>In arappx: appst, appcty, apppol, appcyr
>
>In other words, every field being used for the join is indexed. Here is what I get as the results from SYS(3054,11) when I run the above query:
>
>Rushmore optimization level for table hpaph0: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table hpaph0 using temp index
>
>I am not sure what all of that means, but I know that despite everything being indexed, there is no optimization going on, Fox is building a temp index somewhere, and the query runs slow as a dog. HPAPH0 is only going to get bigger, and it would be nice if this query went fast.
>
>Thanks for taking the time to read and respond. I hope this message is more clear on what is happening...
>
>Thanks,
>JoeK