如何更快地查詢這 2000 萬條記錄視圖？

對於搜尋功能，我使用的視圖包含我需要在其中搜尋的所有表中的記錄。該視圖有近 2000 萬條記錄。針對這種觀點的搜尋花費了太多時間。

我應該在哪裡改進這個視圖的性能？

視圖的粗略定義如下。它包括 13 個表和大約 30 個欄位。

CREATE VIEW [dbo].[v_AllForSearch]
AS
SELECT 
 FT.firstField AS [firstField]
, FT.fld_primary AS [fld_primary]
, FT.fld_thirdField AS [thirdField]
, FT.fld_fourthField AS [fourthField]           
, ISNULL(ST.[fld_firstSearchField],'') AS [firstSearchField]
, ISNULL(TT.[fld_thirdSearch],'') AS thirdSearch
, ISNULL(TT.[fld_fourthSearch],'')AS fourthSearch
, ISNULL(TT.[fld_fifthSearch],'')AS fifthSearch
, ISNULL(FRT.[fld_sixthSearch],'') As [sixthSearch]
, ISNULL(FRT.[fld_seventhSearch],'') AS [seventhSearch]
, ISNULL(FRT.[fld_eightSearch],'')AS [eightSearch]
, ISNULL(FIT.[fld_nineSearch],'') AS [nineSearch]
, ISNULL(SIT.[fld_tenthSearch],'')AS [tenthSearch]
, ISNULL(SET.[fld_eleventhSearch],'') AS [eleventhSearch]
, ISNULL(ET.[twelthSearch],'')AS [twelthSearch]
, ISNULL(NT.[thirteenthSearch],'')AS [thirteenthSearch]
, ISNULL(NT.[fourteenSearch],'') AS [fourteenSearch]
, ISNULL(NT.[fifteenSearch],'') AS [fifteenSearch]
, ISNULL(NT.[sxteenSearch],'')  AS [sxteenSearch]
, ISNULL(NT.[seventeenSearch],'') AS [seventeenSearch]
, ISNULL(NT.[eighteenSearch],'')AS [eighteenSearch]
, ISNULL(TT.[ninteenSearch],'') AS [ninteenSearch]
, ISNULL(ELT.[twentySearch],'') AS [twentySearch]
, ISNULL(ELT.[twentyOneSearch],'') AS [twentyOneSearch]
, ISNULL(TWT.[twentyTwoSearch],'') AS [twentyTwoSearch]
, ISNULL(THT.twentyThree,'') AS [twentyThree]
, ISNULL(THT.twentyFour,'') AS [twentyFour]
, ISNULL(THT.twentyFive,'') AS [twentyFive]
, ISNULL(THT.twentySix,'') AS [twentySix]
FROM 
     tblFirstTable AS FT         
     LEFT JOIN [tblSecondTable] AS ST 
           ON ST.[fld_primary] = FT.[fld_primary]        
     LEFT JOIN [tblThirdTable] AS TT 
           ON TT.[fld_primary] = FT.[fld_primary]        
     LEFT JOIN [tblFourthTable] AS FRT 
           ON FRT.[fld_primary] = FT.[fld_primary]       
     LEFT JOIN [tblFifthTable] AS FIT 
           ON FIT.[fld_primary] = FT.[fld_primary]       
     LEFT JOIN [tblSixthTable] AS SIT 
           ON SIT.[fld_primary] = FT.[fld_primary]       
     LEFT JOIN [tblSeventhTable] AS SET 
           ON SET.[fld_primary] = FT.[fld_primary]       
     LEFT JOIN [tblEighthTable] AS ET 
           ON ET.[fld_primary] = FT.[fld_primary] 
     LEFT JOIN [tblNinthTable] AS NT 
           ON NT.[fld_primary] = FT.[fld_primary]        
     LEFT JOIN [tblELTnthTable] AS TT 
           ON TT.[fld_primary] = FT.[fld_primary]        
     LEFT JOIN [tblEleventhTable] AS ELT 
           ON ELT.[fld_primary] = FT.[fld_primary]       
     LEFT JOIN [tblTwelthTable] AS TWT 
                           ON TWT.[fld_id] = ELT.[fld_id]  
             LEFT JOIN [tblThirteenthTable] AS THT
           ON THT.[firstField]= FT.[firstField]
WHERE fld_Status ..

視圖是擴展的宏。因此，如果您的視圖是 2 個表的 JOIN，則執行計劃將顯示 2 個表。視圖是透明的。

如果視圖被索引/物化，這不適用。但是，您不會問這個問題。

那麼，執行計劃是怎麼說的呢？DTA？缺少索引 dmv 查詢？最昂貴的 dmv 查詢？

如果沒有關於視圖和表的更多詳細資訊，答案是“視情況而定”，但您可以開始查看視圖的 WHERE 子句以查找可能需要索引的欄位。

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