使用完全限定名称会影响性能吗? [英] Does using fully qualified names affect performance?

问题描述

在 SQL Server 中使用完全限定的表名对性能有影响吗?

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

我有一个查询，其中我要加入不同数据库中的两个表.DBA 建议在主机查询中省略数据库名称，我猜这是为了性能或约定.

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.

所有表格完全合格

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

首选?

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

这真的有区别吗?

推荐答案

通常首选完全限定名称，但也有一些注意事项.我会说这在很大程度上取决于要求，一个答案可能无法满足所有情况.

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.

这篇MSDN文章给出SQL Server 最佳实践的公平指导.(检查名为:如何引用对象)

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

此链接更详细地解释了在执行前解析和验证对象引用的一系列步骤:http://blogs.msdn.com/b/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx

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

通过第二个链接，结论是:

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.

此外，如果在生产环境中不允许更改数据库名称，您可能会考虑在完全限定名称中包含数据库名称.

这篇关于使用完全限定名称会影响性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！