Different databases join performance

[Johnny Persson]

Hi,

is it true that joining tables in different databases on the same SQL
server instance is slower than joining tables in the same database?

I read this article about join performance:
http://www.sqlusa.com/articles2005/linkedserver/

I find the results very strange.. This is what the author write:

-- JOIN tables in same database
USE AdventureWorks;
DECLARE @StartTime datetime
DBCC DROPCLEANBUFFERS
SET @StartTime = getdate()
SELECT SlsOrds = count(*)
FROM Sales.SalesOrderHeader soh -- 31K
INNER JOIN Sales.SalesOrderDetail sod -- 121K
on soh.SalesOrderID = sod.SalesOrderID
SELECT DurationMsec = datediff(ms, @StartTime, getdate())
GO
-- 121313
-- 50 msec (avg)

-- JOIN tables in different databases on same server instance
DECLARE @StartTime datetime
DBCC DROPCLEANBUFFERS
SET @StartTime = getdate()
SELECT SlsOrds = count(*)
FROM Sales.SalesOrderHeader soh
INNER JOIN CopyALPHAOfAdventureWorks.Sales.SalesOrderDetail sod --121K
on soh.SalesOrderID = sod.SalesOrderID
SELECT DurationMsec = datediff(ms, @StartTime, getdate())
GO
-- 121313
-- 120 msec (avg)

Comments, thoughts?

Regards,
Johnny

[Erland Sommarskog]

Johnny Persson (a@a.a) writes:
> is it true that joining tables in different databases on the same SQL
> server instance is slower than joining tables in the same database?
>
> I read this article about join performance:
> http://www.sqlusa.com/articles2005/linkedserver/
>
> I find the results very strange.. This is what the author write:

I will have to admit that I was a bit puzzled to. Until I looked at
the query plans. The intra-database query does not access the
Sales.SalesOrderHeader table, whereas the inter-database query does.

The reason the first query skips the header table is because the
optimizer sees that there is a trusted foreign-key constraint, so
it knows that all rows in the details table has a matching row in the
header table. But this is not true for the cross-database query.

Testing performance on SQL Server is by means easy, and it is very
easy to jump to conclusion about what is the reason for the difference
you are seeing. I know. I found earlier tonight that I had made the
same mistake for a number of tests I've been running.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Johnny Persson]

Thank you for your fast answer Erland!