Actually, 2 hours is nothing. My first incarnation of this program, the same routines would take (estimated of course) 6 1/2 days to process. Two hours was a HUGE improvement. Now, the 3-4 minutes for the new selects with joins is much much better. All I have to do now, the current problem, is combine the 3 selects into one then combine that with the original BM table.
>2 hrs ??? Actually I even don't expect it to take 6 mins with scan..endscan approach. SQLs use empty() which is not optimizable (I assume you had indexes on left(zip,5) and other - you might replace the !empty(trim(sddmdata.sdaddr)) expression with :
>!(sddmdata.sdaddr == '') ).
>
>scan approach does one single pass on one of tables (probably you'd want to choose 8500 recs one) and a do case check. It's almost equivalant to one 'optimized' SQL (preparation of indexes might take time - you might even skip that with one table usage with order set to left(zip,5) tag and do some locate while ...for ... But I think indexes + 3 use again would be faster).
>
>You have unnecessary trimming in comparisons. Instead use set ansi and set exact. You might recover extra time doing so too (and possibly you dont need empty() at all).
>Cetin
Peter Brama
West Pointe Enterprises
VFP is getting easier but STILL alot to learn!!