x

Which one is Best either using inner Join or function?

Hi to all ..

I've written two different queries as follows

First One

SELECT VT.VEHICLE_TYPE_NAME 
FROM VEHICLE_MASTER VM
 INNER JOIN VEHICLE_TYPE VT ON VT.VEHICLE_TYPE_SNO = VM.VEHICLE_TYPE_SNO

Second One

  SELECT (SELECT DBO.GET_VEHICLE_TYPE_NAME(VM.VEHICLE_TYPE_SNO)) 
  FROM VEHICEL_MASTER VM

My function is follows

ALTER FUNCTION [DBO].[GET_VEHICLE_TYPE_NAME]
(
 @VEHICLE_TYPE_SNO  AT_INT = NULL
)
RETURNS VARCHAR(100)
AS
BEGIN

RETURN (SELECT VT.VEHICLE_TYPE_NAME

FROM VEHICLE_TYPE VT WHERE VT.VEHICLE_TYPE_SNO = @VEHICLE_TYPE_SNO) END

GO

HERE I'VE TO KNOW PERFORMANCE WISE EITHER INNER JOIN IS BEST OR PREDEFINED FUNCTION IS BEST

PLEASE HELP ME

THANKS TO ALL..........
more ▼

asked Nov 06, 2011 at 01:02 AM in Default

varun 1 gravatar image

varun 1
22 8 8 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
If we compare between scalar value function and table function for requirement then table value function is good for performance point of view but for your requirement I go with join because any how through table value function also we are making join with main table then why not we directly join with main table. So for your requirement Inner join will be faster.
more ▼

answered Nov 06, 2011 at 09:25 PM

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left
To add to @kev riley's answer, you could rewrite your function to be a table value function, calling this using CROSS APPLY. This would perform just as fast as the inner join. You would change the code to return a table, keeping the select the same as you have now, so it is an easy modification to make.
more ▼

answered Nov 06, 2011 at 03:50 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

Inner Join.

The function will be called for every row in VEHICLE_MASTER, whereas the join will be one operation that returns a set of data.

Be aware that the execution plan may show that the function query is 'better', but this will be based on an assumption that the function is called once, returning one row.
more ▼

answered Nov 06, 2011 at 02:03 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840

asked: Nov 06, 2011 at 01:02 AM

Seen: 1551 times

Last Updated: Nov 06, 2011 at 01:03 AM