>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