I am presenting this problem to the “Thread” hoping someone can help speed this view’s REQUERY().

I have changed names of the tables to help simplify (maybe) the question.

Tables and other pertinent info:
Plots: 31 Fields, 310k records. Indexed on the fields ‘plot_id’, and ‘lDeleted’
Watersheds: 3 fields, 7000+ records. Indexed on the field ‘ws_id’
Districts: 2 fields, 10 records. Indexed on the field ‘dist_id’
PlotType: 2 fields, 4 records. Indexed on the field ‘plottype_id’
PlotFilter: 1 Field, currently 310K records. Indexed on the field ‘linkfield’

Plot filter is used to filter the plots the user receives from the Requery() of the view. Its RECCOUNT() can vary between 1 and RECCOUNT(“plots”).

Four tables join to plots (table alias ‘pri’):
JOIN PlotFilter flt ON pri.plot_id = flt.linkfield
JOIN watersheds wss ON pri.ws_id = wss.ws_id
JOIN districts dst ON pri.dist_id = dst.dist_id
JOIN plot_type pt ON pri.plottype_id = pt.plottype_id

The joining table is indexed on the appropriate field (linking field), plots is only indexed on the two fields as stated above.

The view definition:

CREATE SQL VIEW (lcView) AS ;
  SELECT ;
    pri.plot_id, pri.meridian, pri.section, pri.strip, pri.plot_num, ;
    ;
    pri.plottype_id, pt.name as pt_name, ;
    ;
    pri.ws_id, wss.name as ws_name, ;
    ;
    pri.dist_id, dst.name as dist_name, ;
    ;
    pri.latitude, pri.longitude, pri.travel, pri.cruisers, pri.date, ;
    pri.elevation, pri.slope, pri.baf, pri.calc_site, pri.mgmt_site, ;
    ;
    pri.ldeleted ;
  FROM crzplots pri ;
  JOIN PlotFilter flt ON pri.plot_id = flt.linkfield ;
  JOIN watersheds wss ON pri.ws_id = wss.ws_id ;
  JOIN districts dst ON pri.dist_id = dst.dist_id ;
  JOIN plot_type pt ON pri.plottype_id = pt.plottype_id ;
  ORDER BY meridian, section, strip, plot_num

Requerying the view, after opening it NODATA can take up to 30 seconds if all 310k plots are in the filter on a Pentium 4 at 2.4 Ghz. A Pentium II at 450 ghz takes up to 12 minutes.

How can I redo this view to speed the requery? One caveat: Order of the fields in the resulting view is important. I use it for a browse.

BTW: This view was much slower before last week’s inquiry and answer from Sergey ( 1 to 2 minutes). Prior to that, I was using a WEHRE of “pri.plot_id IN (SELECT flt.linkfield FROM plotFlter flt)”

Thank you

tc