Optimized and Efficient Query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Optimized and Efficient Query

Author

Topic

muralik
Starting Member

3 Posts

Posted - 2010-05-04 : 02:46:56

I have written query using functions, here is the sample of the query. It basically calls some functions where it returns ID to name from master table. Request you to let me know if this is efficient method or same thing can be achieved using joins. Which is the recommended, most optimized and efficient way of writing queries.

Insert POS_STR_TEMP1 (T_POS_NAME, T_POS_MASTER_ID, T_CHILD_CNT, T_MASTER_ID, T_POS_FLAG, T_PARENT_ID, T_POS_FUNC_ID)
Select t_pos_des_name, t_pos_des_id, COUNT(t_pos_des_id) as child_count, t_sub_fun_id, 1, dbo.FN_PARENT_CONV(t_sub_fun_id, T_fun_ID),T_sub_fun_id from #POS_STR_TEMP
where T_Sub_Fun_ID in (SElect t_pos_master_id from POS_STR_TEMP1 where T_PARENT_ID IS NOT NULL)
group by t_pos_des_name, t_pos_des_id, t_sub_fun_id, T_fun_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 02:53:40

Yes it would be better to write the subquery as a join.

Something like this:
Insert POS_STR_TEMP1 (T_POS_NAME, T_POS_MASTER_ID, T_CHILD_CNT, T_MASTER_ID, T_POS_FLAG, T_PARENT_ID, T_POS_FUNC_ID)
Select t.t_pos_des_name, t.t_pos_des_id, COUNT(t.t_pos_des_id) as child_count, t.t_sub_fun_id, 1, dbo.FN_PARENT_CONV(t.t_sub_fun_id, t.T_fun_ID),t.T_sub_fun_id
from #POS_STR_TEMP t
JOIN POS_STR_TEMP1 t1
ON t.T_Sub_Fun_ID = t1.t_pos_master_id
WHERE t1.T_PARENT_ID IS NOT NULL
group by t.t_pos_des_name, t.t_pos_des_id, t.t_sub_fun_id, t.T_fun_ID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-04 : 02:56:01

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 03:07:54

Great blog post! That's interesting that your tests show that IN is faster than INNER JOIN. We did performance testing between two stored procedures and found the INNER JOIN to be faster. It was a while back though, and unfortunately I didn't take notes or write a blog about it. I do remember that we changed the code to use INNER JOIN though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

muralik
Starting Member

3 Posts

Posted - 2010-05-04 : 03:34:25

Just a extension to the above query, I will post one other query. Thanks for your quick reply to the earlier one.

In the below query I have used some functions which are listed below, these functions uses different master tables and provides their names and short names when input argument 'id' is provided.

a) dbo.FN_FUNCTION_ID_TO_NAME(function_id)
b) dbo.FN_FUNCTION_ID_TO_NAME(sub_functionid)
c) dbo.FN_POSITION_ID_TO_NAME(pos_des_id)
d) dbo.FN_GRADE_ID_TO_NAME(GRADE_ID)

This whole query can be alternately re-written using joins with the master table.

Request you to let me know which is the most efficient, optimised and reliable method to do the same.

Insert #POS_STR_TEMP (T_Fun_id, T_Fun_Name, T_Sub_Fun_ID, T_Sun_Fun_Name,T_POS_des_ID,T_POS_DES_NAME, T_POS_grade_ID, T_POS_GRADE)
Select distinct function_id, dbo.FN_FUNCTION_ID_TO_NAME(function_id),
sub_functionid, dbo.FN_FUNCTION_ID_TO_NAME(sub_functionid),
pos_des_id, dbo.FN_POSITION_ID_TO_NAME(pos_des_id),
grade_id, dbo.FN_GRADE_ID_TO_NAME(GRADE_ID)
from PNET_POS_PDSTR where LOB_ID = @POS_LOB_ID
group by function_id, SUB_FUNCTIONID, pos_des_id, GRADE_ID;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 03:35:57

You will need to test it in order to determine which one is better. Put both in the same batch and compare the execution plan and the cost. Comment out the insert so that they can be run in the same batch without issues.

Let us know your testing results.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-04 : 03:55:10

Don't just check the exec plan's cost. Use Statistics IO and Statistics Time and check the CPU time.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-04 : 03:56:42

quote:
Originally posted by tkizer

Great blog post! That's interesting that your tests show that IN is faster than INNER JOIN.

The difference was small enough that I'm not sure it's significant. It does, however, appear consistent at all the row counts that I tested, and more pronounced at higher ones.

http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources