Improving performance of query by inlining function into SQL 1

[Custom24]

Hi
Three tables. Synthesis_Plates, with a one to many relationship to Synthesised_Compounds, which in turn has a one to many relationship to Anal_Plates_Compounds

My query - for each synthesis_plate, pull back some information from the synthesis_plate table, along with the number of related entries in the anal_plates_compounds table.

Currently, I am going about it this way, because it works and doesn't hurt my brain

SELECT PLATE_NAME, DATE_SYNTHESIS_BEGAN, CHEMIST, AVERAGE_PLATE_MASS, BATCH_NUMBER, numCompsFromSynPltInAnalEver(ID) AS EVERANALYSED,
FROM SYNTHESIS_PLATES SP

And the function numCompsFromSynPltInAnalEver I've defined in PL/SQL as follows;

CREATE OR REPLACE function numCompsFromSynPltInAnalEver(numSynthesis_Plate_ID in number) return number
is
numCountInAnalysis number;
begin
--this return the number of compounds from a particular synthesis plate "in analysis" at the moment
select
count(sc.SYNTHESIS_PLATE_ID) into numCountInAnalysis
from
synthesised_compounds sc, anal_plates_compounds apc, analytical_plates ap
where
apc.SYN_COMPOUND_ID = sc.ID and ap.ID = apc.ANAL_PLATE_ID
and sc.SYNTHESIS_PLATE_ID = numSynthesis_plate_id;

return numCountInAnalysis;
end;
/

But I am thinking there has to be a more efficient (faster) way to do this without the use of the function, ie directly with one SQL statement

Can anyone help?

Thanks

Mark

 

[jee]

You can do it with an embedded view, something like:

[/code]
SELECT PLATE_NAME, DATE_SYNTHESIS_BEGAN, CHEMIST,
AVERAGE_PLATE_MASS, BATCH_NUMBER,
EVERANALYSED,
FROM SYNTHESIS_PLATES SP,(
select sc.SYNTHESIS_PLATE_ID,
count(sc.SYNTHESIS_PLATE_ID) AS EVERANALYSED
from synthesised_compounds sc,
anal_plates_compounds apc,
analytical_plates ap
where apc.SYN_COMPOUND_ID = sc.ID
and ap.ID = apc.ANAL_PLATE_ID
group by sc.SYNTHESIS_PLATE_ID) X
WHERE x.SYNTHESIS_PLATE_ID = SP.ID
[/code]

 

[greekgirl]

Try using a sub query to make it one query.

SELECT PLATE_NAME, DATE_SYNTHESIS_BEGAN, CHEMIST, AVERAGE_PLATE_MASS, BATCH_NUMBER, EVERANALYSED,
FROM SYNTHESIS_PLATES SP,
(
select sc.synthesis_plate_id,
count(sc.SYNTHESIS_PLATE_ID) EVERANALYSED
from
synthesised_compounds sc, anal_plates_compounds apc, analytical_plates ap
where
apc.SYN_COMPOUND_ID = sc.ID and ap.ID = apc.ANAL_PLATE_ID
Group By sc.SYNTHESIS_PLATE_ID) plate
WHERE sp.ID = sc.SYNTHESIS_PLATE_ID;

 

[Custom24]

Thanks jee - I tried to post this solution earlier myself, because I figured it out. But the site was down.
But here's a star for you anyway


Mark