I think I've figured out that this is where a big part of my problem is. Not enough memory.
The basic design of the query just uses too much. The sum(t2.field) on so many records uses too much to keep it fast.
When I applied different filters to reduce the # of records from t1 the query ran fast.

I now have to redesign the data so that I can permanently quit checking to make sure sum(t2.amount) = t1.amount.

Thanks to all who helped.

>Hey Bill
>
>Select FieldList, sum(Field1) ;
> from t1 ;
> where not in (select from t2) ;
> group by Field3
>
>Doesn't it seem cleaner to exclude the aliases. I only include them when I need to to prevent confusion. The not in may speed things up. Please let me know.
>
>Thanks
>
>>>Hilmar,
>>>
>>>I've discovered something else. This probably isn't a Rushmore problem after all.
>>>I'll start here before starting another thread.
>>>
>>>Here's my original SQL that got the job done, but was slow.
>>>I always made sure that t1 and t2 were open before the SQL was executed.
>>>I'm working on it now on a stand alone machine (no network).
>>>

>>>Select t1.FieldList, sum(t1.Field1) ;
>>>  from t1 left join t2 ;
>>>  on t1.<SameField> = t2.<SameField> ;
>>>  where !t1.Field2 ;
>>>  group by t1.Field3
>>>  having isnull(t2.<SameField>)
>>>

>>>I've discovered that it only runs slow the first time (whether from the command window or a prg). As much as 36 seconds depending on what else is open on the computer. If I run it again (in the command window hit UPARROW and ENTER, it runs fast. As fast as 1.6 seconds.
>>>
>>>Also, if I switch windows in Windows 98 (say to my browser) and switch back to VFP, then hit UPARROW and ENTER it runs slow again. I don't do anything in the browser, just switch windows and back again.
>>>
>>>I've tried finding another query that behaves similarly, but I can't find any. (I've only spent about 5 minutes so far). They always run within 5% or so of the original speed (as long as the tables are open already).
>>
>>I've just discovered something else.
>>I had 2 fields in each table that were filled with bintoc(fk1) + bintoc(fk2,1). I was going to start using them as SameField. I removed them and the problem got better. Still didn't go away, but got better.

Bill Morris