内部联接与标量函数 [英] inner join Vs scalar Function

问题描述

以下查询哪个更好...这只是一个例子，在很多情况下，我希望显示用户名而不是UserID

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

或

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

如果使用第一种方法没有性能上的好处，那么我宁愿使用第二种方法...我在用户表中将有2000条记录，在电子邮件表中将有100k条记录...

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...

谢谢

推荐答案

这是一个很好的问题，很高兴思考SQL性能等问题.

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

从纯SQL的角度来看，第一个更好.在第一个语句中，它可以通过联接在单个批处理命令中完成所有操作.在第二个中，对于trn_misc_email中的每一行，必须运行单独的BATCH select来获取用户名.这可能会导致现在或将来出现性能问题

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.

这篇关于内部联接与标量函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！