Hi richard,
There is probably something wrong with the indexes. The best thing you could do is to drop all indexes and see what performance you have.
After that add indexes for each column used in a JOIN.
If this seems not to be the problem, the problem might be in the configuration of the hardware, network or OS.
I've done lots of queries of this size and it should be possible (depending on hardware, width of tables, netwerk, storage etc) to do this in less than half a minute to a few seconds.
Walter,
>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
>
