Performance issue encountered need another approac

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance issue encountered need another approac

Author

Topic

benjaminins
Starting Member

1 Post

Posted - 2005-07-03 : 05:45:58

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-03 : 15:38:01

Start off by reading this:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you post like that, we can answer a lot quicker and more accurately. The way you posted the code makes replies more difficult.

The reason the functions are slowing your code down is because they are being called for every single row in your SELECT statement. You have two. So, if you are returning 5000 records, you're running a function 10000 times.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-03 : 15:53:16

also, try to use real names for your tables and columns instead of tbl1, tbl2, tbl3, record1, record2, etc.

I read your post once and probably won't spend any more time on it because it is not very clear.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources