Database

1. Make sure you have proper indexes on your tables.
   
2. Look at using some inner (or outer) joins, depending on what data you are trying to return.
   

tonyong wrote:

I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this

SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
    prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
    prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
  ON prcd.prcdname = ppar_engparameters.prcdname
  AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
  ON catg_pll.partprcdname = prcd.prcdname
  AND catg_pll.partprcdversion  = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'

--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
     (parmval, activeflag)

select distinct ppar_engparameters.parmval, ppar_engparameters.parmname,<br />
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatus, <br />
catg_pll.catgnumber, catg_pll.category<br />
from ppar_engparameters<br />
inner join prcd<br />
on prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A'<br />
inner join catg_pll<br />
on catg_pll.partprcdname = ppar_engparameters.prcdname and catg_pll.partprcdversion = <br />
ppar_engparameters.prcdversion<br />
where ppar_engparameters.parmval='MyValue'

<br />
SELECT catnumber, category <br />
FROM catg_pll<br />
where catg_pll.partprcdname = prcd.prcdname AND catg_pll.partprcdversion = prcd.prcdversion <br />

Johan Lombaard wrote:

Has anyone seen this ?