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)
