Do you have an index on DELETED() for each of the tables? What's the setting of SET DELETED?
Do you have an index on OBSOLETE in your HUD_SEGMENTS table? (I ask this because you're checking it in your WHERE clause).
--Brad
>I know this is a rather complex SQL join but can it be optimized? Every primary key (PK) and foreign key (FK) is indexed. I am getting zero Rushmore optimization.
>
>
> SELECT hc.order AS cat_order ,;
> hs.order AS seg_order ,;
> hc.name AS category ,;
> hc.linkname ,;
> hc.shortname ,;
> CAST( REPLICATE( " ", hs.indent ) + ;
> IIF( FILE( [hud/svg/lg] + TRANSFORM( hs.pk, [@L ] + REPLICATE( '9' , ;
> m.nNines ) ) + ".svg" ), ;
> [<a href="svg/lg] + TRANSFORM( hs.pk, [@L ] + REPLICATE( '9' , ;
> m.nNines ) ) + [.svg] + [" onClick="wopen('svg/lg] + ;
> TRANSFORM( hs.pk, [@L ] + REPLICATE( '9' , m.nNines ) ) + ;
> [.svg'); return false;"> ] + ALLTRIM( hs.name ) + [</a>], ;
> ALLTRIM( hs.name ) ) AS CHAR( 200 ) ) AS segment ,;
> hs.type ,;
> hs.description ,;
> hs.url ,;
> hs.italic ,;
> f1.number AS cat_footfootnum ,;
> f2.number AS seg_footfootnum ,;
> SUM( CAST( IIF( hdate.pk = <<This.aDates[1,2]>>, hdtl.value, NULL ) ;
> AS Numeric( 16,2 ) ) ) AS wk1 ,;
> SUM( CAST( IIF( hdate.pk = <<This.aDates[2,2]>>, hdtl.value, NULL ) ;
> AS Numeric( 16,2 ) ) ) AS wk2 ,;
> SUM( CAST( IIF( hdate.pk = <<This.aDates[3,2]>>, hdtl.value, NULL ) ;
> AS Numeric( 16,2 ) ) ) AS wk3 ,;
> SUM( CAST( IIF( hdate.pk = <<This.aDates[4,2]>>, hdtl.value, NULL ) ;
> AS Numeric( 16,2 ) ) ) AS wk4 ,;
> SUM( CAST( IIF( hdate.pk = <<This.aDates[5,2]>>, hdtl.value, NULL ) ;
> AS Numeric( 16,2 ) ) ) AS wk5 ;
> FROM hud_categories hc ;
> INNER JOIN hud_segments hs ;
> ON hc.pk = hs.hud_categoriesfk ;
> LEFT JOIN hud_details hdtl ;
> ON hdtl.hud_segmentsfk = hs.pk ;
> LEFT JOIN hud_dates hdate ;
> ON hdtl.hud_datesfk = hdate.pk ;
> LEFT JOIN hud_footnotes f1 ;
> ON f1.pk = hc.hud_footnotesfk ;
> LEFT JOIN hud_footnotes f2 ;
> ON f2.pk = hs.hud_footnotesfk ;
> WHERE NOT hs.obsolete ;
> GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ;
> ORDER BY 1, 2 ;
> INTO CURSOR (m.cAlias)
>
