>>Change the INNER JOINS to WHERE clauses. For whatever reason, the query engine takes longer to process joins than it takes to process the same results from a where statement.
>>
>>Just a thought.
>>
>>>This view is currently taking about
2.5 minutes to load.
>>>Can anyone help me speed up this select/view?
>>
>>*snip*
>
>I already tried that and it came out about the same.
Okay I've got another thing to try.
You are pulling 31385 records (or so) from your tables. It may not be your query that is causing the slowness but the amount of data retrieved. Try a small test.
Use the same select criteria but change the fields you retrieve. Start out with something small like:
SELECT Orderitems.order_id, ;
Orders.order_po, ;
Supplier.code ;
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 1,2,3
See if this doesn't improve the performance. Even taking a minimalist approach, if you have over 31000 and the average record size is 120 bytes, you are pulling 3.72 MB over your network connection. Given some slow LAN connections, this could take some time. You can extrapolate if your result set record size is larger than this.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao