查看跨多個數據庫，但只使用一個查詢很慢

給定一個像這個例子這樣的視圖，在同一個數據庫集群中跨越多個數據庫，每個數據庫都有一個唯一的INSTANCE_NO。

請注意，每個數據庫都可以使用 INSTANCE_NO 進行拆分。此外，每個表只有大約 100 行。

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)

對於只訪問一個數據庫的查詢，我有一個巨大的性能問題：

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

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

我用過 RECOMPILE，NOLOCK，……我不知道，誰能幫助我？

編輯：解決 方案與問題一樣愚蠢的解決方案：轉到每個數據庫的屬性-> 選項-> 自動。將“自動關閉”設置為 false

答案：

轉到每個數據庫的屬性-> 選項-> 自動。

將“自動關閉”設置為 false

沒關係，

我剛去了 sp_executesql

性能問題消失了，可讀性消失了

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

引用自：https://dba.stackexchange.com/questions/199819