If you have SET DELETED OFF and SET ANSI OFF, you probably need tags on Deleted() for each table.
>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
>
