0

I have a view that is now joining two other views with some extra tables. It's very slow.

My experience tell me it's because views are not indexed by default. I tried to create an index on each of them, but it's not possible since they have self joins or inner queries.

My question is: It appears to me that in general the join of views is not recommended. So, in short, there is no way to reuse a code from one view into another? Example: the view A calculates the percentage and the view B calculates something else that uses the percentage from view A plus other information from other tables/views. What would be the best approach? Do you really have to replicate the code from view A to view B so it uses the original table's indexes?

Views (simplified view, to show the issue):

View A (calculates the percentage):

SELECT     dbo.tblPopAgeGrp.RevID, dbo.tblPopAgeGrp.VarID, dbo.tblPopAgeGrp.LocID, 
                      dbo.tblPopAgeGrp.TimeID, dbo.tblPopAgeGrp.AgeID, tPAGT.AgeID AS AgeTotal, 
                      100 * dbo.tblPopAgeGrp.PopMale / tPAGT.PopMale AS PopMalePerc, 
                      100 * dbo.tblPopAgeGrp.PopFemale / tPAGT.PopFemale AS PopFemalePerc, 
                      100 * dbo.tblPopAgeGrp.PopTotal / tPAGT.PopTotal AS PopTotalPerc
FROM         dbo.tblPopAgeGrp
INNER JOIN   dbo.tblPopAgeGrp tPAGT
        ON dbo.tblPopAgeGrp.GroupID = tPAGT.GroupID
        AND dbo.tblPopAgeGrp.AgeID = 700

View A by itself, since there so many records, takes a long time to execute. However, in view B the records are filtered according to the VersionID.

View B (gets the percentage from view A with additional info from another view):

SELECT     vPAGP.VersionID, 
           vPAGP.LocationID AS LocID, 
           vPAGP.PopTotalPerc  AS pPopTot,
           vPAGP.PopMalePerc   AS pMale,
           vPAGP.PopFemalePerc AS pFemale,
           vPAGPSR.PopMaleSexRatio    AS SexRatio,
           vPAGPSR.PopFemaleSexRatio  AS FemRatio
FROM       dbo.vwA AS vPAGP
INNER JOIN dbo.vwOther AS vPAGPSR
           ON vPAGPSR.GroupID = vPAGP.GroupID
WHERE vPAGP.VersionID=10

Executing View A without filters, takes like 10 minutes. Executing it for VersionID=10 only, it executes in 10 seconds. The view vwOther executes very quickly.

Thanks!

igorjrr
  • 790
  • 1
  • 11
  • 22
  • I take it these are not indexed views? could they become indexed views? http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server – xQbert Dec 05 '14 at 16:56
  • The views should take advantage of underlying indexes on the tables used in the view. – Gordon Linoff Dec 05 '14 at 16:57
  • I figured the query optimizer would be smart enough to use those indexes anyway, but if the query becomes too complex, it won't work anymore. I'd inspect the explain plan and see if there are any indexes to add or change on any of the tables. – GolezTrol Dec 05 '14 at 16:59

1 Answers1

2

You are not correct when you state "It appears to me that in general the join of views is not recommended."

Views can be combined with other views and will perform well provided that all JOINs are optimizable and have the appropriate index created and any filtering done within the view is optimizable and has appropriate indexes created.

A view based on other views should perform as well as the same query written to factor out the views. If you want further help, please post the definition of all views involved in your problem.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Thank you for your answer! I've included some more information to show what I mean. – igorjrr Dec 05 '14 at 19:48
  • The key point is: the VersionID filtering apparently is being applied after the whole View A is produced, which takes a long time. If I execute View A separately with VersionID assigned, it executes in 10 seconds. – igorjrr Dec 05 '14 at 19:55
  • Thanks for your answer, again. I noticed that changing the order of the inner joins and moving filters to the ON clause instead of WHERE clause had an important impact in the performance. – igorjrr Dec 05 '14 at 21:32