>>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
>>
>>

>>SELECT	Orderitems.order_id, ;
>>	Orders.order_date, ;
>>	Orderitems.closed, ;
>>	Orders.ofaxclnum, ;
>>	ALLTRIM(Orders.last_name)+", "+ALLTRIM(Orders.first_name) AS subject, ;
>>	Orderitems.report_nam, ;
>>	Orders.order_po, ;
>>	Supplier.code, ;
>>	Orderitems.descriptio, ;
>>	Orderitems.city, ;
>>	Orderitems.state, ;
>>	Orderitems.county, ;
>>	Orderitems.reply, ;
>>	Orderitems.summary, ;
>>	Orderitems.status, ;
>>	Orderitems.user_id, ;
>>	Orderitems.report_num, ;
>>	PADL(ALLTRIM(Orderitems.report_num),LEN(Orderitems.report_num),"0") AS rpt_num ;
>>   FROM  ofax!orders INNER JOIN ofax!orderitems ;
>>	INNER JOIN ofax!supplier ;
>>		ON  Orderitems.sup_id = Supplier.sup_id ;
>>	ON  Orders.order_id = Orderitems.order_id ;
>>   ORDER BY Orders.ofaxclnum, 18, Orderitems.report_num
>>

>>
>>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
>>

>
>Hi Richard,
>
>First time I saw your select statement, I noticed a little problem, which I see was not mentioned by others. You use alltrim(expr1)+', '+alltrim(expr2). It will give you the first record width of the subject. You better padr it to the fixed length. This is just a general observation (the first thing, that caught my attention), I haven's studied this SQL in details.
>
>HTH


Thanks, you are right.
Ed Raugh caught it also...his recomendation was to try this....

SELECT	Orderitems.order_id, ;
	Orders.order_date, ;
	Orderitems.closed, ;
	Orders.ofaxclnum, ;
<B>	Orders.last_name-(", "+Orders.first_name) AS subject, ;</B>
	Orderitems.report_nam, ;
	Orders.order_po, ;
	Supplier.code, ;
	Orderitems.descriptio, ;
	Orderitems.city, ;
	Orderitems.state, ;
	Orderitems.county, ;
	Orderitems.reply, ;
	Orderitems.summary, ;
	Orderitems.status, ;
	Orderitems.user_id, ;
	Orderitems.report_num, ;
	CHRTRAN(Orderitems.report_num,' ',"0") AS rpt_num ;
  FROM  ofax!orderitems ; 
        INNER JOIN ofax!orders ON Orderitems.order_id = Orders.order_id ;
	INNER JOIN ofax!supplier ON Orderitems.sup_id = Supplier.sup_id ; 
 ORDER BY 4, 18, 17