SQL Server performance and fully qualified table names

Question

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

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? 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)?

Answer 1

Try it out? Just loop through a million queries of both and see which one finishes first.

I'm guessing it's a load of bunk though. The developers of MS SQL spend millions of hours researching efficiency for search algorithms and storage methods only to be thwarted by users not specifying fully qualified table names? Laughable.

Answer 2

As Spencer said - try it, of course make sure you clear the cache each time as this will interfere with your results.

http://www.devx.com/tips/Tip/14401

I also would be suprised if it made any apprecible difference.

Answer 3

SQL server will not make an extra look up if the default schema is the same. It should be included if it's not and it's a query that is used a lot.

The database name will not benefit the query performance. I think this could be seen with the Estimated Execution Plan in the Management Studio.

Answer 4

In this case I would personally prefer readability over the tiny performance increase that this could possibly cause, if any.

SELECT * FROM Foo

Seems a lot easier to scan than:

SELECT * FROM MyDatabase.[dbo].Foo

Answer 5

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