>Nadya,
>
>The WHERE filtering happens AFTER the records are JOINed. This can cause the creation of a very large temporary file, depending upon the number of records and fields in the tables being joined.

The result of this query is 2 records (since it has GROUP BY clause). Using sys(3054,11) I see partial optimization for PropMstr and SiteMstr. We may implement Data Warehouse for one time jobs, but currently we're doing selects from all tables joined. I can not split this query, so I'm thinking about adding more indexes...

Do you think, it would be a wise decision to add indexes on StNum (I), LotCode (C 1), Unit (C 6) to SiteMstr?

>
>You might try creating quieries of the tables you're JOINing into cursors with only the records and fields you need, and then joining these cursors. It can be a lot faster.
>
>HTH
>
>Dan
>
>>Hi everybody,
>>
>>Here is a query, which I want to optimize:
>>

>>SELECT count(.T.) AS GBCount, ;
>>       sitemstr.ccode  AS ccodegrp  ;
>>       FROM propmstr ;
>>       inner JOIN sitemstr on propmstr.propid = sitemstr.propid ;
>>       left JOIN bldgmstr on propmstr.propid = bldgmstr.propid ;
>>       where  Prefcode = "P" and INLIST(SiteMstr.ccode,"10","01") ;
>>       and ZIPCODE<>"     " and (StNum>0 or (LotCode="C" and Unit<>"      ")) ;
>>       and LIKE("*=*",OWNER1) and inlist(PropMstr.SellName,"P","Y") ;
>>       and BETWEEN(LstMtgDate,{^1998-12-01},{^2000-12-30}) and ;
>>       BETWEEN(LstMtg,200000,500000) and InactvFl#"A" ;
>>       GROUP BY ccodegrp  INTO TABLE ..\..\work\query\qryR0KWT4F

>>
>>I excluded two joins from this query, which makes it even more complicated.
>>
>>PropMstr has indexes on PropID, LstMtgDate, LstMtg. Deleted
>>SiteMstr has indexes on PrefCode, and ZipCode. Deleted
>>
>>This query takes 153 sec. to run with set deleted on (this setting should be used).
>>
>>SiteMstr is ~ 4mln. recs, PropMstr is ~ 3.5 mln. recs
>>
>>What would be your suggestions? Addint indexes on StNum, LotCode, Unit in SiteMstr? SellName is 1 ch. field in PropMstr, do we need to have index on it?