Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ..
share|improve this question

5 Answers 5

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.

share|improve this answer
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 '11 at 15:31
4  
@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. –  David Spillett Feb 21 '11 at 21:02

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?

share|improve this answer
    
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 '11 at 2:45
    
@Joe: possibly, but then OP wouldn't be asking for help if they knew the differences... –  gbn Feb 22 '11 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 '11 at 18:22
1  
@AndrewSQL: I did. But we should cater for the lower life forms... –  gbn Feb 24 '11 at 19:59

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?

share|improve this answer

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

share|improve this answer
    
These details sounds promising.Let me try those and will get back on the results. –  balu Feb 25 '11 at 9:39

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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