How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking too much time.

Where should I look to improve the performance of this view?

The rough definition for the view is below. It includes thirteen tables and around thirty fields.

```
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]

A view is macro that expands. So if your view is a JOIN of 2 tables, the execution plan will show the 2 tables. The view is transparent.

This doesn't apply if the view is indexed/materialised. However then you wouldn't be asking this question.

So, what does the execution plan say? The DTA? Missing indexes dmv query? Most expensive dmv query?

StackExchange Database Administrators Q#1374, answer score: 9