Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

inner join Vs scalar Function

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

like image 472
The King Avatar asked Sep 01 '10 09:09

The King


People also ask

Can we use scalar function in join?

Scalar valued function return single value (not table) so you cannot use joins.

Is inner join more efficient?

You may be interested to know which is faster – the LEFT JOIN or INNER JOIN. Well, in general INNER JOIN will be faster because it only returns the rows matched in all joined tables based on the joined column.

What is the difference between scalar functions and aggregate functions?

Aggregate functions operate against a collection of values and return a single summarizing value. Scalar functions return a single value based on scalar input arguments. Some scalar functions, such as CURRENT_TIME, do not require any arguments.

Is there a difference between join and inner join?

Difference between JOIN and INNER JOINJOIN returns all rows from tables where the key record of one table is equal to the key records of another table. The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.


2 Answers

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.

like image 69
Paul Hadfield Avatar answered Oct 10 '22 09:10

Paul Hadfield


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.

like image 35
Martin Smith Avatar answered Oct 10 '22 11:10

Martin Smith