'What is the best approach for performance when querying across multiple DB's
We have a setup where our customers each have their own databases. We also have some shared databases that are used to hold things like module access, reports, customer server locations, etc.
We have a few queries that look like this
USING CustomerDB
SELECT
fields
FROM
CustomerTable C
INNER JOIN SharedDb.dbo.SharedtableA A ON A.Id = C.SharedAId
INNER JOIN SharedDb.dbo.SharedtableB B ON B.Id = A.SharedBId
Does it make a difference to query plans etc if we were to change the query so that it executes in separate spaces?
E.g
USE CustomerDb
DECLARE @SharedTemp TABLE (
Id int NOT NULL
)
INSERT INTO @SharedTemp
SELECT
Id
FROM
SharedDb.dbo.SharedtableA A
INNER JOIN SharedDb.dbo.SharedtableB B ON B.Id = A.SharedBId
SELECT
fields
FROM
CustomerTable C
INNER JOIN @SharedTemp A ON A.Id = C.SharedAId
Thank you in advance for your insights
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
