I have had excellent results, speed-wise, when splitting a SELECT - SQL into several parts. First one table into a cursor, then combine the cursor with a second table, etc. Perhaps you can try this?
>I have used the coverage profiler and found that the slowest line of code is when this view is requeried.
>
>
>SELECT Ordprod.orderid, Ordsize.orderprodid,;
> SUM(Ordsize.ordered) AS totordered,;
> SUM(Ordsize.allocated) AS totallocated,;
> SUM(Ordsize.shipped) AS totshipped, Ordhead.sbo AS specialord;
> FROM ;
> Ordsize ;
> INNER JOIN ordprod ;
> ON Ordprod.orderprodid = Ordsize.orderprodid ;
> INNER JOIN ordhead ;
> ON Ordprod.orderid = Ordhead.orderid;
> GROUP BY Ordsize.orderprodid, Ordprod.orderid, Ordhead.sbo;
> ORDER BY Ordsize.orderprodid
>
>
>This view returns about 20,000 records
>
>ordsize table has 194,000 records
>ordprod table has 21,000 records
>ordhead table has 3600 records
>indexes exist for all fields, i.e. orderprodid,orderid in all tables
>
>This query takes about 2-3 seconds on my local machine, but can take minutes over the network at the customer site.
>
>Any ideas on how to speed this up?
>Mike
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)