内连接与标量函数
下面哪个查询更好...这只是一个例子，有很多情况，我希望显示用户名而不是 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 条记录...
谢谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是一个很好的问题，很高兴能够思考 SQL 性能等问题。
从纯 SQL 的角度来看，第一个更好。在第一个语句中，它能够通过连接在单个批处理命令中完成所有操作。在第二个中，对于 trn_misc_email 中的每一行，必须运行单独的 BATCH 选择来获取用户名。这可能会导致现在或将来出现性能问题。
对于其他参与该项目的人来说，阅读起来也更容易，因为他们可以看到正在发生的情况。如果您有第二个，那么您必须去查看该函数（我猜这就是它的含义）以找出它正在做什么。
所以实际上有两个理由使用第一个理由。
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.
内联 SQL
JOIN通常为 比标量 UDF 更好，因为它可以更好地优化。测试时请务必使用 SQL Profiler 查看两个版本的成本。
SET STATISTICS IO ON不会在其图中报告标量 UDF 的成本，这会使标量 UDF 版本看起来比实际情况更好。The inline SQL
JOINwill 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 ONdoesn't report the cost for scalar UDFs in its figures which would make the scalar UDF version appear better than it actually is.标量 UDF 非常慢， 但是内联的速度要快得多，通常与联接和子查询一样快
顺便说一句，您使用函数调用进行查询相当于外联接，而不是内联接。
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.
为了给您提供更多帮助，请注意，在 SQL Server 中使用管理工作室，您可以通过
显示估计执行计划来评估性能。它显示了索引和连接的工作原理，您可以选择最佳的使用方式。您还可以使用 DTA（数据库引擎优化顾问）获取更多信息和优化。
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.