使用完全限定的名称会影响性能吗?

Does the use of fully qualified table names in SQL Server have any affect on performance?

在SQL Server中使用完全限定的表名是否会对性能产生影响?

I have a query where I am joining two tables in different databases. A DBA has suggested to omit the database name on the host query, which I am guessing is either for performance or a convention.

我有一个查询，我正在连接不同数据库中的两个表。一位DBA建议省略主机查询上的数据库名，我猜这可能是为了性能，也可能是为了约定。

All tables fully qualified

完全限定的所有表

USE [DBFoo]
SELECT * FROM [DBFoo].[dbo].[people] a
INNER JOIN [DBBar].[dbo].[passwords] b on b.[EntityID] = a.[EntityID]

Preferred?

喜欢吗?

USE [DBFoo]
SELECT * FROM [dbo].[people] a
INNER JOIN [DBBar].[dbo].[passwords] b on b.[EntityID] = a.[EntityID]

Does this actually make a difference?

这真的有影响吗?

4 个解决方案

#1

Fully qualified names are usually preferred, but some considerations apply. I will say it depends a lot on the requirements and a single answer may not suffice all scenarios.

完全限定的名称通常是首选的，但是需要考虑一些因素。我想说，这在很大程度上取决于需求，一个单一的答案可能不能满足所有的情况。

Note that this is just a compilation binding, not an execution one. So if you execute the same query thousand times, only the first execution will 'hit' the look up time, which means lookup time is less in case of fully qualified names. This also means using fully qualified names will save the compilation overhead (the first time when query is executed).

注意，这只是一个编译绑定，而不是执行绑定。因此，如果您执行相同的查询一千次，那么只有第一次执行将“命中”查找时间，这意味着在完全限定名称的情况下查找时间更短。这也意味着使用完全限定的名称将节省编译开销(第一次执行查询时)。

The rest will reuse the compiled one, where names are resolved to object references.

其余的将重用已编译的名称，其中名称解析为对象引用。

This MSDN Article gives a fair guidance on SQL Server best practices. (Check the section named: How to Refer to Objects)

本文提供了关于SQL Server最佳实践的公平指导。(查看命名部分:如何引用对象)

This link explains in more details on set of steps done to resolve and validate the object references before execution: http://blogs.msdn.com/b/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx

这个链接详细解释了在执行之前解决和验证对象引用的步骤集:http://blogs.msdn.com/b/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005- default-schema-setting.aspx

Going through the second link, the conclusion says that:

通过第二个环节，结论是:

Obviously the best practice still stands: You should fully qualify all object names and not worry about the name resolution cost at all. The reality is, there are still many imperfect applications out there and this setting help great for those cases.

显然，最佳实践仍然存在:您应该完全限定所有对象名称，而不必担心名称解析成本。现实是，仍然有许多不完善的应用程序，这个设置对这些情况有很大帮助。

Also, in case the database name change is not allowed on production environment, you may then think to include database names in fully qualified names.

此外，如果在生产环境中不允许更改数据库名称，您可能会认为应该将数据库名称包含在完全限定的名称中。

#2

Does the use of fully qualified table names in SQL server have any affect on performance?

在SQL server中使用完全限定的表名是否会对性能产生影响?

There's a trivial penalty because the query text is longer so there's more bytes to be sent to SQL Server and parsed.

因为查询文本的时间更长，所以将有更多的字节被发送到SQL Server并解析。

The penalty is academic, honesty it will not be better or worse because of the prefixes.

惩罚是学术性的，诚实它不会因为前缀而变得更好或更坏。

If you observe a difference in performance, it may be because the query text is different and SQL Server has generated a different plan. If the conditions (statistics, whatever else) do not change between running the queries then in all likelihood SQL Server will generate a 100% identical plan. If conditions have changed between the prefixed and unprefixed queries being run then one plan may be better than another.

如果您观察到性能上的差异，可能是因为查询文本不同，SQL Server生成了不同的计划。如果在运行查询时条件(统计信息等)不变，那么SQL Server很可能会生成一个100%相同的计划。如果正在运行的前缀查询和未前缀查询之间的条件发生了变化，那么一个计划可能比另一个计划更好。

Although in that scenario, the performance difference is not because of the prefixing. If you evicted the plans from the plan cache and ran them again (thus giving SQL Server a chance to generate plans under the same conditions) you should see both queries with the same plan.

尽管在这种情况下，性能差异并不是因为前缀。如果您将计划从计划缓存中删除并再次运行它们(从而使SQL Server有机会在相同的条件下生成计划)，那么您应该会看到两个查询具有相同的计划。

There is significance for qualifying object names (see CREATE VIEW ... WITH SCHEMABINDING) but there aren't consequences for performance.

对对象名进行限定是有意义的(请参阅CREATE VIEW…)使用SCHEMABINDING)但是对性能没有影响。

#3

Having DB prefix will cause issue if you migrate or rename the DB Name. That could be the reason why DBA advised so

如果迁移或重命名DB名称，使用DB前缀将导致问题。这可能是DBA如此建议的原因

#4

Does the use of fully qualified table names in SQL Server have any affect on performance? Yes. Reusing plan caches eliminates requirement to "recompile" a plan.

在SQL Server中使用完全限定的表名是否会对性能产生影响?是的。重用计划缓存消除了“重新编译”计划的需求。

BTW: Research parameter sniffing so plan reuse doesn't adversely impact performance... the flip side.

顺便说一句:研究参数嗅探，所以计划重用不会对性能产生负面影响……另一面。

MSDN: The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

MSDN:将新的SQL语句匹配到缓存中现有的、未使用的执行计划的算法需要所有对象引用都是完全限定的。例如，第一个SELECT语句不匹配现有计划，第二个SELECT语句匹配:

SELECT * FROM Person;

SELECT *的人;

SELECT * FROM Person.Person;

从Person.Person SELECT *;

Source: https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

来源:https://technet.microsoft.com/en-us/library/ms181055(v = sql.105). aspx

So, MSDN considers schema.name as fully qualified name, which matches. Not convinced this correlates with best practice of aliasing tables, required for intellisense to be helpful... muddy waters.

因此，MSDN将schema.name视为完全限定名，与之匹配。不相信这与混叠表的最佳实践相关联，需要智能感知来帮助……浑水。

#1