Hello guys,

I have this query, and its taking over 25 min to return the results, I know its somehow a complex query and most of the tables it looks in has over 500K rows, in exception of ops.xml_count that has arround 250 rows

here it is.

select distinct T2.VALFROM,T2****NTIME,
(select count(valfrom) as GBLI_COLLATERAL from GBLI.COLLATERAL where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_COMPONENTGROUP from GBLI.COMPONENTGROUP where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_FEATUREDETAIL from GBLI.FEATUREDETAIL where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_PRODSTRUCT from GBLI.PRODSTRUCT where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_PRODFLAG from GBLI.PRODFLAG where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_WWATTRIBUTES from GBLI.WWATTRIBUTES where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_WWCOMPONENTS from GBLI.WWCOMPONENTS where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as GBLI_WWPRODUCT from GBLI.WWPRODUCT where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(countrycode) as EACM_FINALPRICE from EACM.FINALPRICE where lastupdated between (T2.VALFROM) and (T2****NTIME)),
(select count(country) as EACM_FINAL_CATPRODREL from EACM.FINAL_CATPRODREL where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(country) as EACM_FINAL_COMPAT from EACM.FINAL_COMPAT where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(nlsid) as EACM_FINAL_FEATURE from EACM.FINAL_FEATURE where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as EACM_FINAL_PRODUCT from eacm.FINAL_PRODUCT where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as EACM_FINAL_PRODPRODREL from eacm.FINAL_PRODPRODREL where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as EACM_FINAL_WWATTRVALUE from EACM.FINAL_WWATTRVALUE where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as EACM_FINAL_WWCOMPAT from EACM.FINAL_WWCOMPAT where valfrom between (T2.VALFROM) and (T2****NTIME)),
(select count(valfrom) as EACM_FINAL_WWPRODPRODREL from EACM.FINAL_WWPRODPRODREL where valfrom between (T2.VALFROM) and (T2****NTIME))

from ops.xml_count T2 with ur

What are your counts like, how much data are you counting? Do you have indexes on all of these tables to support the WHERE clause, meaning an index on VALFROM and a couple of them with valfrom, country or valfrom, nlsid or lastupdated, countrycode?

Dave Nance