I have not read the entire thread, but...
Select each of the tables to local cursor first, then perform the joins and Group By off the cursor tends to help performance. The actual work is done locally, sometimes from cursors in memory.
>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
Greg Reichert