Thomas --

Actually, I can create any number of cursors to filter from cDrillDown -- it's a teensy cursor where an additional row is added each time the user drills down another level. In only very unusual circumstances would it have more than five records or so in it.

The records can cause it to read sub-queries from any of a few different free tables, one of which is CustomerSummary (there's also PartSummary, etc.) Most of my WHERE phrase is created at runtime, depending on the contents of other fields in cDrillDown. There will be one sub-query for each unique value of Depth in cDrillDown.

The only table of any size at all is SalesSummary -- all the other free tables and cursors are teensy.

Now, as for JOIN vs IN ... (first of all, I'm sure I don't even understand what JOIN does without something on the left, and I find the documentation particularly opaque) ... If I have a list of, say, 100 customer numbers and I want to select all records from a large table that are in this list, that using the JOIN syntax is faster than IN?

Thanks

Jim


>If I read this correctly you create to separate cursors to filter from cDrilldown.
>Depending on your data distribution this could lead to duplicate data traveling down the wire, if each of the cursors is large and the cross-section is small. Also I prefer a Join operation to an In (Select ..) if I am worried about performance.
>
>So I would

>Select cs.CustNumber as FustNumber from CustomerSummary cs;
>     Join cDrillDown as T_1 on Left(T_1.DrillValue,20) = cs.ACustClass ;
>     Join cDrillDown as T_2 on Left(T_2.DrillValue,15) = cs.ACustID ;
>     Where T_1.Depth = 1 and T_2.Depth = 2 ;
>     Into Cursor T_C
>
>Select {some_fields} from SalesSummary   ;
>       join T_C on FustNumber = CustNumber ;
>       Where Between( period, m.lnFirstPeriod, m.lnLastPeriod) ;
>       into cursor cBrowse readwrite             ;
>       group by {some_other_fields}

>
>and then try out building one statement... Check with sys(3054, 12) that no temporary indices are created, which vfp will sometimes do - then creating the index once in the table eliminates that step.
>
>HTH
>
>thomas