inner join Vs Function

Question

Hi all,

Which of the following query is better... This is just an example, there are numerous situations, where I want the user name to be displayed instead of UserID

Select  EmailDate, B.EmployeeName as [UserName], EmailSubject
    from Trn_Misc_Email as A
         inner join 
         Mst_Users as B on A.CreatedUserID = B.EmployeeLoginName

or

Select  EmailDate, GetUserName(CreatedUserID) as [UserName], EmailSubject
    from Trn_Misc_Email

If there is no performance benefit in using the First, I would prefer using the second... I would be having around 2000 records in User Table and 100k records in email table...

Thanks

Answer 1

A good question and great to be thinking about SQL performance, etc.

From a pure SQL point of view the first is better. In the first statement it is able to do everything in a single batch command with a join. In the second, for each row in trn_misc_email it is having to run a separate BATCH select to get the user name. This could cause a performance issue now, or in the future

It is also eaiser to read for anyone else coming onto the project as they can see what is happening. If you had the second one, you've then got to go and look in the function (I'm guessing that's what it is) to find out what that is doing.

So in reality two reasons to use the first reason.

Answer 2

The inline SQL JOIN will usually be better than the scalar UDF as it can be optimised better.

When testing it though be sure to use SQL Profiler to view the cost of both versions. SET STATISTICS IO ON doesn't report the cost for scalar UDFs in its figures which would make the scalar UDF version appear better than it actually is.

Answer 3

To help you more, just a tip, in SQL server using the Managment studio you can evaluate the performance by Display Estimated execution plan. It shown how the indexs and join works and you can select the best way to use it.

Also you can use the DTA (Database Engine Tuning Advisor) for more info and optimization.

Answer 4

Scalar UDFs are very slow, but the inline ones are much faster, typically as fast as joins and subqueries

BTW, you query with function calls is equivalent to an outer join, not to an inner one.