>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