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

5 Answers 5


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?

  • He might be asking the question for a materialized view, and not realize it's typically implemented as just another table, so can be indexed, etc.
    – Joe
    Feb 22, 2011 at 2:45
  • @Joe: possibly, but then OP wouldn't be asking for help if they knew the differences...
    – gbn
    Feb 22, 2011 at 4:37
  • The question is tagged for MS SQL Server so rather than "materialized views" we should be talking about "indexed views" ;)
    – AndrewSQL
    Feb 23, 2011 at 18:22
  • 1
    @AndrewSQL: I did. But we should cater for the lower life forms...
    – gbn
    Feb 24, 2011 at 19:59

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.

  • 1
    But I was under the impression that views in general don't benefit greatly from indexes (... according to "I was told by this guy I know")
    – jcolebrand
    Feb 21, 2011 at 15:31
  • 5
    @jcolebrand : views in general are helped greatly by indexes, depending on how they are used. Essentially when used in a given query they will benefit as if their code were inserted directly into the query. For simple views+queries this means they use indexes as well as any simple query would. For more complex views/queries it depends how well the query planner can rearrange and optimise the work to be done. The best way to see this is to pick a large dataset and manufacture some example views and queries using them, and see what SSMS's query plan display says the QP does with them. Feb 21, 2011 at 21:02

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

  • These details sounds promising.Let me try those and will get back on the results.
    – balu
    Feb 25, 2011 at 9:39

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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.