>Maybe we're being fussy...
>
>Hello,
>I have an application that queries on fairly large (250,000 - 1.2M records). The queries can have conditions upon any number of fields from one or two related tables. The output of the SELECT is a cursor containing the minimum fields needed to reference back into the original tables (for reports, etc...). My experience is that when the result set is small (SELECT returns a few thousand records or less), this process is very fast. However, if the result set is large (same conditions, only now SELECT returns up to *all* records for this
>table), the process is very slow; and, in fact, adding index tags (yes, optimized) for the conditions in an attempt to speed up the select does almost nothing (probably because most all records meet the conditions anyway (my theory).
>Given that all is optimized, since the query time seems proportional to the size of the result set, my guess is that the creation of the result cursor is where the time is used. In fact, I have done a few tests that indicate that a SELECT .. WHERE .. INTO CURSOR .. from a single table is roughly as fast as a COPY .. FOR .. TO ...
>Is there a way to select from related tables that gets around this?
>
>I'm not necessarily asking for a solution, maybe just a change in philosophy. ;)
>
>As always, many thanks for any help!
>Mark

My assumption is that there are two reasons why larger output significantly delays SELECT-query:
1) Larger size of output cursor implicitly prompts VFP to create the physical cursor on hard-drive (not in RAM).
2) If you have multiple filter/joins, then realistically VFP will apply them step by step, i.e. apply first criteria, get bunch of recors (temp cursor), apply second criteria on this bunch i.e. potentially temp reindexing it, etc, therefore time for this temp reindexing can differ significantly for larger recordsets.
In regard to related tables: there are certain situations when you may get much better speed by substituting JOINs in SELECT with purely interface solution, i.e. you run SELECT against single master file and in form/report pick up lookup fields by SEEK on request (UDF).