Dan,

Unfortunately, this SQL is generated by special Query application, and what's why I can not easily split it. I understand your idea and agree. I'll discuss it with my manager. I think, we're going in creating Data Warehouse direction for these type of jobs too (we already have DW for monthly and weekly jobs, these are "one-timers")...

I'll discuss idea of adding indexes...

Thanks again.

>Nadya,
>
>>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...
>
>I don't know the considerations there, but if you're running this SQL from code or from a view, I'm not sure what the disadvantages are of creating 3 or 4 separate SQL statements or views and running them together. I'm not talking about Data Warehousing, but just running 4 SELECT statements instead of 1.
>
>To explain in a little more detail, the join is executed first before any grouping and before and WHERE filtering. So, if you're joining 1mil records to 1mil records in a one-to-one relationship, you're creating a temporary cursor with 1mil records with ALL of the records and ALL of the fields from both tables. If it's a one-to-many relationship, it could be much, much larger. This temporary table is then filtered with the WHERE clause and then it's grouped.
>
>The main speed issue here is probably not Rushmore related, but the time it takes to create and write the large temporary table to the hard drive.
>
>If instead, you first SELECT only the records and fields you need from the tables into cursors, and then run your SELECT -- without the need for most of your WHERE -- on those cursors you may only be joining a small subset of records.
>
>Since the initial SELECTs don't have any JOINs, they have no need to create any temporary tables except for the final cursor. The speed savings depends upon what percentage of the records your WHERE is filtering and what percentage of the fields you're using.
>
>I'm not sure if this is an accurate translation of what you're trying to do, but it should give you the general idea:
>
>
>

>SELECT sitemstr.propid, sitemstr.ccode
>	FROM sitemstr;
>	WHERE INLIST(SiteMstr.ccode,"10","01") and;
>	INTO CURSOR c_siremstt
>
>
>SELECT propid
>     FROM propmstr ;
>	WHERE Prefcode = "P" 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"
>	INTO CURSOR c_propmstr
>
>
>SELECT count(.T.) AS GBCount, ;
>       	c_siremstt.ccode  AS ccodegrp  ;
>	FROM c_propmstr ;
>     	inner JOIN c_siremstt on;
>			c_propmstr.propid = c_siremstt.propid ;
>		left JOIN bldgmstr on;
>			c_propmstr.propid = bldgmstr.propid ;
>	GROUP BY ccodegrp  INTO TABLE ..\..\work\query\qryR0KWT4

>
>
>
>>Do you think, it would be a wise decision to add indexes on StNum (I), LotCode (C 1), Unit (C 6) to SiteMstr?
>
>Looks like it would help with the SQL either way you do it.