Performance issue encountered need another approach

[benjamins]

--------------------------------------------------------------------------------

This is my table structrue

Tbl1 -----Record1-----Record2----- Record3
Tbl1_id --------1------------2-------------3

Tbl2 -----Record1-----Record2----- Record3
Tbl2_id --------1------------2-------------3
Tbl1_id --------1------------1-------------1

Tbl3 -----Record1-----Record2----- Record3
Tbl3_id -------1-------------2-------------3
Tbl2_id -------1-------------2-------------3
Tbl3_Name ---Test1---------Test2--------Test3
Tbl3_Flag ----N-------------Y-------------Y

Tbl4 -----Record1-----Record2----- Record3
Tbl4_id -------1-------------2-------------3
Tbl2_id -------1-------------2-------------3
Tbl4_Name ---Temp1--------Temp2--------Temp3
Tbl4_Flag ----N-------------Y-------------Y

I wanted the result as

Tbl3_Name--Tbl4_Name--Tbl3_Flag--Tbl4_Flag
Test1 -----------Temp2--------Y-----------Y

The rule is like this
I have to pick a record which satisfies

Tbl3_Name must be picked iff Tbl3_Flag=Y
Tbl4_Name must be picked iff Tbl4_Flag=Y

If I have more than one record in Tbl3 or Tbl4 which has Flag Y then I can choose the first such record which satisfies the above condition.

I wrote a query like

select fn_Tbl3_Name(tbl1_id), fn_Tbl4_Name(tbl1_id), Tbl3_Flag, Tbl4_Flag, from tbl1, tbl2, tbl3, tbl4 where tbl1_id=tbl2.tbl1_id and tbl2_id=tbl3.tbl2_id and tbl2_id=tbl4.tbl2_id

fn_Tbl3_Name(tbl1_id Number)
ret_name varchar(40);
begin
select tbl3_name from tbl3, tbl2 where tbl2_id=tbl1_id and tbl2_id=tbl3.tbl2_id and tbl3_Flag='Y' and rownum=1;
return ret_name;
end

I did the same in the second function... this worked but the performance was poor...

I have more than 5000 records in my table also this is only a part of my complex query... Initally my query took 15 to 17 seconds to fetch the records but after I introduced these function it took 90 secs which is awfully bad...

I need something that will do this opertaion very quickly... it can take a maximum of 35 secs...

please help me

[Kate Albany]

Hi benjamins,

Try this:

select tbl3.tbl3_name,
tbl4.tbl4_name,
tbl3.tbl3_flag,
tbl4.tbl4_flag
from tbl2,
tbl3,
tbl4
where tbl2.tbl2_id = tbl3.tbl2_id
and tbl2.tbl2_id = tbl4.tbl2_id
and tbl3.tbl3_flag = 'Y'
and tbl4.tbl4_flag = 'Y'
and rownum = 1

Also you want to make sure that your tables all have appropriate indexes to ensure a fast response.

Kate