>>This view is currently taking about 2.5 minutes to load.
>>Can anyone help me speed up this select/view?
>>It's composed of 3 tables:
>> Orders (9851 reocrds)
>> OrderItems (31385 records)
>> Supplier (10 records)
>>
>>The result table/view should end up with 31385 records
>>
>>Orders has an index on Order_id
>>OrderItems has an index on Order_id and an index on Sup_id
>>Supplier has an index on Sup_id
>
>
>Richard, in my experience, Rushmore does a poor job of optimizing joins between 3 or more tables.
>
>Try using a SELECT on Orders and OrderItems only with a function reference to get the supplier code id. The function should do a seek on Supplier.Sup_Id and return Supplier.Code.
>
>If you try this, I would love to know if it helped.
>
>Peter

Peter, I think I did that already.....did you overlook this section of my original post or am I still missing something?

<I>
If I split it into two it still takes about 2 minutes

Even this short one takes 35 seconds

SELECT	orders.*, ;
	Orderitems.* ;
   FROM  ofax!orders INNER JOIN ofax!orderitems ;
	ON  Orders.order_id = Orderitems.order_id ;
   INTO CURSOR temp


SELECT	Temp.order_id_a, ;
	Temp.order_date, ;
	Temp.closed, ;
	Temp.ofaxclnum, ;
	ALLTRIM(Temp.last_name)+", "+ALLTRIM(Temp.first_name) AS subject, ;
	Temp.report_nam, ;
	Temp.order_po, ;
	Supplier.code, ;
	Temp.descriptio, ;
	Temp.city, ;
	Temp.state, ;
	Temp.county, ;
	Temp.reply, ;
	Temp.summary, ;
	Temp.status_b, ;
	Temp.user_id_b, ;
	Temp.report_num, ;
	PADL(ALLTRIM(Temp.report_num),LEN(Temp.report_num),"0") AS rpt_num ;
   FROM  Temp INNER JOIN ofax!supplier ;
		ON  Temp.sup_id = Supplier.sup_id ;
   ORDER BY temp.ofaxclnum, rpt_num, temp.report_num
</I>