SQL Server 性能和完全限定的表名 [英] SQL Server performance and fully qualified table names

问题描述

在查询中包含架构所有者似乎可以提高数据库性能，例如:

It seems to be fairly accepted that including the schema owner in the query increases db performance, e.g.:

SELECT x FROM [dbo].Foo vs SELECT x FROM Foo.

这应该是为了保存查找，否则 SQL Server 将在连接上下文中查找属于用户的 Foo 表.

This is supposed to save a lookup, because SQL Server will otherwise look for a Foo table belonging to the user in the connection context.

今天有人告诉我，始终包含数据库名称会以同样的方式提高性能，即使您正在查询您在连接字符串中选择的数据库:

Today I was told that always including the database name improves the performance the same way, even if you are querying the database you selected in your connection string:

SELECT x FROM MyDatabase.[dbo].Foo

这有什么道理吗?这作为编码标准有意义吗?这些(即使是第一个例子)是否会转化为可衡量的收益?

Is there any truth to this? Does this make sense as a coding standard? Does any of this (even the first example) translate to measurable benefits?

我们是在讨论在数据库服务器上进行额外字典查找的几个周期，还是在 Web 服务器(或其他客户端)上进行更臃肿的 SQL 和额外连接?

Are we talking about a few cycles for an extra dictionary lookup on the database server vs more bloated SQL and extra concatenation on the web server (or other client)?

推荐答案

要记住的一点是，这是一个编译绑定，而不是一个执行绑定.因此，如果您执行相同的查询 100 万次，只有第一次执行会命中"查找时间，其余的将重用相同的计划并且计划是预先绑定的(名称已经解析为对象 ID).

One thing to keep in mind is that this is a compilation binding, not an execution one. So if you execute the same query 1 million times, only the first execution will 'hit' the look up time, the rest will reuse the same plan and plans are pre-bound (names are already resolved to object ids).

这篇关于SQL Server 性能和完全限定的表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！