Thomas --

Thanks for all your help on this. Without going into the specifics, I removed all the "Where IN" clauses with JOINs, and, as you indicated, was able to reduce the number of joins necessary where multiple selections where actuall from the same table.

Even on my local machine, where I've been getting very fast response, I can tell the difference.

Thanks again,

Jim


>Hi James,
>>
>>In my case, my CustSummary file is really small ... less than 1,000 records, and the CustNumber field is a four-byte integer. I'm not really concerned at all with processing on this file, or the other similar files (other than SalesSummary).
>
>I am coming from insurance data - millions of records. On very small data sets my advice might be even slower, as vfp caches such data in cursors totally in memory<g>. But for your sizes speed should never be a problem, unless you invoke in a loop for reporting or somehow you get a cartesian product<bg>.
>
>>
>>The INLIST looks interesting, altho it is possible for the user to select as many entries to go into my cDrillDown as desired -- so they could go past 26, although that would be highly unusual. In any case, I don't think that's the issue you're getting at.
>
>That is coming right out of insurance data: imagine a base table of 3*10**7 recs to be filtered on 4 tables, each resulting in filtering out 90% of the data to get an export cursor of 3*10**3 records. The first intermediate table (of 3) has 3*10**6 records and that takes time to write out. If I can find only 1 filter table, where I can create an optimizable condition like inlist(), the first intermediate table (of 2) will have only 3*10**5 records (1 condition rushmoring base table + 1 filter table joined on index), which is a mighty step forward, as it probably can be cached, giving it another speed boost.
>
>>So, I think I'm beginning to get it. It seems like you're saying that if I have a list of CustNumbers in an indexed cursor,
>>

Select blabla from SalesSummary where CustNumber in (select * from cList)

is not as good as
>>

Select blabla from SalesSummary join cList on SalesSummary.CustNumber = cList.CustNumber

>
>Yes, but only if it is a distinct list of CustNumbers ! Beware of duplicate lines...
>
>regards
>
>thomas