View crossing multiple databases, but a query only using one is slow Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.

Please note that each database can be split using the INSTANCE_NO
Also, each table has only about 100 rows.

ALTER VIEW [dbo].[VI_MULTI_DUMMY]  AS
SELECT 1 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM [HUB_1].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 2 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_2].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 4 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_4].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)


I have a huge performance problem for a query that only accesses one database:

--&gt; 0 sec
SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] , ... [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

--&gt; 5-7 sec
select T.* from VI_MULTI_DUMMY AS T  WITH(NOLOCK) where  INSTANCE_NO = 3 OPTION(RECOMPILE)


I've used RECOMPILE, NOLOCK, ... and I'm out of idea's, who can help me?

EDIT : SOLUTION 
A solution as stupid as the problem :
Go to each database it's properties--> Options--> Automatic.
Set 'Auto Close' to false
Never mind,

I just went with sp_executesql

Performance issue are gone, readability gone

SET @myInst = '[db2Name]'
SET @sqlReplaced = REPLACE('myQry','{@myInst}',@myInst)

EXECUTE sp_executesql
@statement                  = @sqlReplaced,
@ParmDefinition             = @ParmDefinition,

@clientNo                   = @clientNo,
@lastTransactionDate                = @lastTransactionDate,
@msInstanceNo               = @msInstanceNo, 
@dateFrom                   = @dateFrom , 
@dateUntil                  = @dateUntil