How do I query this 20 million record view faster?

Question

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

Answer 1

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?

Answer 2

Without more details about the view and tables the answer is "it depends", but you could start looking at the WHERE clause of your view for the fields that may require indexes.

Answer 3

In addition to what others have said (WHERE clause, INDEXes that might help) I suggest you might want to consider indexed views - assuming it's even possible to create indexes on the view (details). Then you may be able to also apply the NOEXPAND hint in your queries (details).

Answer 4

The generic answer is to take a look at the execution plan. Are your joins indexed? Are your output fields included in those indexes? Are you only outputting the columns you need to see?

Answer 5

What I would probably do, is just create 2 views

• The 1st view is just of the fields I need to search; just those fields. I would return the ID field for each row, plus what kind of table your searching for. I did a similar thing by creating a UNION ALL view that searched multiple tables. I just made sure to include the id, the type, and the text fields, i wanted to search.

• The 2nd view would handle displaying the results gathered in the 1st view, and would have every table you need to display results, or perhaps instead of a view, make it a stored procedure.

I would do a UNION ALL, with a GROUP BY at the bottom, and I would not do all those LEFT OUTER JOINs.