What's the best way - 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 2005 Forums

Transact-SQL (2005)

What's the best way

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2010-06-15 : 17:52:53

Hi,

Lets' say i have 3 tables for the following example:
- Orders
- Customers
- Parameters

And i need to get the list of orders with order stage description and customer information

Questions:
----------
1. Does it make any difference at the performance Level between these 2 statements ?

- "Inner Join customers c on c.customer_id = o.customer_id"
and
- Inner Join customers c on o.customer_id = c.customer_id

2. Which Method is more performant to Get Order Stage Description:

Method 1: Create a view on the fly in the "From Section"
---------
Select o.order_id
, o.order_date

-- Get Order Stage Information
, o.stage
, vw_stage.description as stage_description

, o.sub_total
, o.gst
, o.pst
, o.total

, o.customer_id
, c.customer_code
, c.name
, c.address1
, c.address2
, c.phone1

From orders o
Inner Join customers c on c.customer_id = o.customer_id
Inner Join
(
Select type
, description
From parameters
Where code = 'ORDER_STAGE'
) vw_stage on o.stage = vw_stage.type

Method 2: Call a function that return a specific description for each record
---------

create function dbo.fu_get_description
(
@p_code nvarchar(30),
@p_type nvarchar(10)
)
returns nvarchar(300)
Begin

Declare @v_description nvarchar(300)

Select @v_description = description
From parameters
Where code = @p_code
and type = @p_type

return @v_description

End

Select o.order_id
, o.order_date

-- Get Order Stage Information
, o.stage
, dbo.fu_get_description ('ORDER_STAGE', o.stage) as stage_description

, o.sub_total
, o.gst
, o.pst
, o.total

, o.customer_id
, c.customer_code
, c.name
, c.address1
, c.address2
, c.phone1

From orders o
Inner Join customers c on c.customer_id = o.customer_id

Thank you

Paul

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 18:34:33

1 = No, SQL will use the exact same Query Plan whichever way round your write the join criteria

2 = Definitely NOT the function.

Can't you just JOIN to the [parameters] table?


Select o.order_id
...
, vw_stage.description as stage_description
 o.sub_total
...
From orders o
Inner Join customers c on c.customer_id = o.customer_id
Inner Join parameters AS vw_stage ON vw_stage.type = o.stage AND vw_stage.code = 'ORDER_STAGE'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-15 : 18:36:23

My initial response to your inquiries is, "What did you observe when you tried these different approaches?"

As for question #1: I can't believe that there is any difference in execution to be seen from changing the order in the "equals" comparison. However, you could verify this by looking at the execution plans for the two queries.

Question #2: The issue with passing the value into a function is that you will need to invoke the function for every row returned. The first approach you list is a set-based solution whereas the second approach is an iterative solution.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources