Help with Query

[UltraSmooth]

I have a query that is working but runs extremely slow so I'm trying to re-write it to improve performance.

My query involves 3 tables. SYINVHDR and SYINVDTL store item detail information by category and location while SYINVHIS stores sales summary information.

My query gets a set of items from the inventory tables, including the current onhand and onorder and then joins it to a sales summary for a time period. I need to join the syinvhdr and syinvdtl tables separately to get a total onhand & onorder by item/location first then join this with the sales summary by item/location as to include the onhand value only once in the summary.

Here's my working query,

SELECT details.id_style,details.ih_desc,details.ih_sbclass,details.ih_dept,details.ih_class,details.ih_vendor,details.id_acost,details.id_lcost,SUM(details.id_curonh) as onhand,SUM(details.id_onorder) as onorder,SUM(his_salcost) as salesCost,SUM(his_salretl) as salesRetail,SUM(his_salunit) as salesUnits
FROM
(SELECT id_style,id_colour,id_sze,id_width,id_loctn,id_acost,id_lcost,id_curonh,id_onorder,ih_desc,ih_sbclass,ih_dept,ih_class,ih_vendor
FROM syinvdtl JOIN syinvhdr ON id_style=ih_style AND ih_sbclass='HDG') AS details
LEFT JOIN
(SELECT
his_key,SUM(his_salcost) as his_salcost,SUM(his_salretl) as his_salretl,SUM(his_salunit) as his_salunit,id_style,id_colour,id_sze,id_width,id_loctn
FROM syinvhdr
JOIN syinvdtl
ON syinvhdr.ih_style=syinvdtl.id_style AND ih_sbclass='HDG'
JOIN syinvhis
ON CONCAT(RPAD(id_style,10,' '),RPAD(id_colour,5,' ' ),RPAD(id_sze,3,' '),RPAD(id_width,3,' '))=syinvhis.his_key AND syinvdtl.id_loctn = syinvhis.his_loctn
AND syinvhis.his_period='W' AND syinvhis.his_type='IT' AND (syinvhis.his_tdate>=[START_DATE] AND syinvhis.his_tdate<=[END_DATE])
GROUP BY his_key,his_loctn
ORDER BY his_key,id_loctn) as sales
ON sales.id_style=details.id_style and sales.id_colour=details.id_colour and sales.id_sze=details.id_sze and sales.id_width=details.id_width and sales.id_loctn=details.id_loctn
GROUP BY details.id_style,details.ih_desc,details.ih_sbclass,details.ih_dept,details.ih_class
ORDER BY ih_sbclass,ih_dept,ih_class,salesRetail DESC;

My explain on the query gives me this,

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL {null} {null} {null} {null} 44561 Using temporary; Using filesort
1 PRIMARY <derived3> ALL {null} {null} {null} {null} 12433
3 DERIVED syinvhdr ref PRIMARY,Index_sbclass Index_sbclass 3 12222 Using where; Using index; Using temporary; Using filesort
3 DERIVED syinvdtl ref PRIMARY,ndx_loctn PRIMARY 12 jpos.syinvhdr.ih_style 2 Using index
3 DERIVED syinvhis ref PRIMARY,his_tdate PRIMARY 36 func,jpos.syinvdtl.id_loctn 6 Using where
2 DERIVED syinvhdr ref PRIMARY,Index_sbclass Index_sbclass 3 12222 Using where
2 DERIVED syinvdtl ref PRIMARY PRIMARY 12 jpos.syinvhdr.ih_style 2

Is there anyway I can improve this query? When dealing with 10-20,000 items this takes minutes to run. Any help is appreciated.

 

[ingresman]

Open question, is it too slow or do you just want to make it faster ?

 

[UltraSmooth]

It's really too slow. People are complaining about the speed of the report, it takes minutes to run.